<?php
/**
*
* @package phpBB3
* @version $Id$
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/

/**
*/

/**
* @ignore
*/
if (!defined('IN_PHPBB'))
{
	exit;
}

include_once($phpbb_root_path . 'ofc/php-ofc-library/open-flash-chart.' . $phpEx);
include_once($phpbb_root_path . 'sv_common_fb.' . $phpEx);
include_once($phpbb_root_path . 'sv_common_page.' . $phpEx);
include_once($phpbb_root_path . 'sv_constants.' . $phpEx);

// get time from date format (Ymd). Currently we don't need this function. 
function get_time_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return 0;
		
	$y = (int)substr($date, 0, 4);
	$m = (int)substr($date, 4, 2);
	$d = (int)substr($date, 6, 2);
	
	if ($y < 1970 || $m > 12 || $d > 31)
		return 0;
		
	$t = ($y - 1970) * 365.242199 * 24 * 60 * 60 + (($m - 1) * 30.4368499 + $d - 1) * 24 * 60 * 60;
	
	return $t;
}
// get date in format (Ymd) from time. 
function get_date_from_time($time)
{
	return date('Ymd', $time);
}

// Adjust timezone between Taiwan and Bluehost hosts for date() function. 
function get_taiwan_time()
{	
	// The Amazon EC2 server uses UTC+0 timezone, so we just add 8 here.
	// We should use a better way to get propoer time. 
	return time() + 8*60*60;
	
	
	// As I develop in Taiwan, so add this for convenience.
	if (PHP_OS == 'WINNT')
		return time() + 8*60*60;
		 
	// Check if the server is not DST. 
	// According to the United States Naval Observatory, daylight savings time begins the Second Sunday of March and ends on the First Sunday of November.
	// The code peice is from Tower Of Power ( http://www.toosweettobesour.com/2009/03/10/calculating-daylight-savings-time-boundary-in-php/ ).
	$remove_hour = strtotime("Second Sunday March 0");  
	$add_hour = strtotime("First Sunday November 0");  
	$time  = time();  
	if( $time >= $remove_hour && $time < $add_hour )  
	{
		// in DST.
		return time() + 14*60*60;	
	}  
	else  
	{  
		// not in DST.
		return time() + 15*60*60;
	}	
}

// convert the date string in database to the normal format.
// EX: from 20101101 to 2010/11/01
function get_date_string($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$y = substr($date, 0, 4);
	$m = substr($date, 4, 2);
	$d = substr($date, 6, 2);
	
	return $y . '.' . $m . '.' . $d;
}

// Get the string to show the time info on pages.
function get_time_string($time)
{
	return date('Y.m.d H:i', $time);
}

// get year from a date (format: YYYYMMDD).
function get_year_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$y = substr($date, 0, 4);
	
	return $y;
}

// get month from a date (format: YYYYMMDD).
function get_month_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$m = substr($date, 4, 2);
	
	return $m;
}

// get day from a date (format: YYYYMMDD).
function get_day_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$d = substr($date, 6, 2);
	
	return $d;
}

// Check if the corp id is valid. 
function is_valid_corp_id($corp_id)
{
	$len = strlen($corp_id);
	
	if ($len < 4 || $len >6)
		return false;
	
	return true;
}

function is_valid_date($date)
{
	$len = strlen($date);
	
	if ($len != 8)
	{
		return false;
	}
		
	$y = (int)(substr($date, 0, 4));
	$m = (int)(substr($date, 4, 2));
	$d = (int)(substr($date, 6, 2));
	
	if ($y > 2000 && $m > 0 && $m < 13 && $d > 0 && $d < 32)  	
		return true;
		
	return false;	
}

function is_iOS($http_user_agent)
{
	$os_array 	= array(
		'/iphone/i',
		'/ipod/i',
		'/ipad/i',
		);
		
	foreach ($os_array as $o)
	{
		if (preg_match($o, $http_user_agent))
			return true;	
	}
	
	return false;
}

// print service log header.	
function print_service_log($file)
{
	global $print_service_log_start_time;
	global $task_start_time, $task_exec_time;
	 
	$task_start_time = array();
	$task_exec_time = array();
	
	$print_service_log_start_time= get_taiwan_time();
	
	echo PHP_EOL . '=========================================' . PHP_EOL;
	echo $file . PHP_EOL;
	echo date('Y/m/d H:i:s', $print_service_log_start_time) . PHP_EOL . PHP_EOL;
}

// print current execution time to service log.	
function begin_service_task($str = '')
{	
	global $task_start_time;
	
	$task_start_time[$str]	= get_taiwan_time();
	
	echo PHP_EOL . "Task Start : $str, " . PHP_EOL;
	echo 'TW  ' . date('Y/m/d H:i:s', $task_start_time[$str]) . PHP_EOL . PHP_EOL;
	echo '--------=========================-------' . PHP_EOL ;
}

// print current execution time to service log.	
function end_service_task($str = '')
{
	global $task_start_time;
	global $task_exec_time;
	global $db;
	
	if ($str == '')
		return;
	
	$task_exec_time[$str]	= get_taiwan_time() - $task_start_time[$str];

	echo PHP_EOL . "Task Run Time: $str, " . ($task_exec_time[$str]) . ' seconds ' . PHP_EOL;
	echo '--------=========================-------' . PHP_EOL . PHP_EOL . PHP_EOL;
	
	// insert the task info to DB	
	$sql = 'INSERT INTO sv_service_task (task, begin_time, end_time) VALUES ("' . $str . '" ,' .  $task_start_time[$str] . ' ,' . get_taiwan_time() . ')';
    $db->sql_query($sql);
}

// Check if a service task was already completed for the latest day.
//
// Parameter:
// only_trading_day:	default as true and only trading day is counted. Set to false to include non-trading day. 
//
function service_task_completed_for_latest_trading_date($str, $only_trading_day = true)
{
	global $db;
		
	if ($str == '')
		return false;
	
	if ($only_trading_day)
		$now_date = get_latest_trading_date(get_taiwan_time());
	else
		$now_date = get_date_from_time(get_taiwan_time());
	
	// we use begin_time rather than end_time here in case the complete task taking so long that it runs accross 2 trading days.
	$query		= 'SELECT * FROM sv_service_task WHERE task="' . $str . '" ORDER BY begin_time DESC'; 
	$result		= $db->sql_query_limit($query, 1);
	if ($row = $db->sql_fetchrow($result))
	{
		$begin_time = $row['begin_time'];
		$begin_date = get_latest_trading_date($begin_time);		
		
		if ($only_trading_day)
			$begin_date = get_latest_trading_date($begin_time);	
		else
			$begin_date = get_date_from_time($begin_time);	
		
		if ($now_date == $begin_date)
		{
			if ($only_trading_day)
				echo "task $str already completed for trading date $now_date \n";	
			else
				echo "task $str already completed for date $now_date \n";		
			
			return true;
		}
	}
	
	return false;  
}

// print service log footer.
function print_service_log_end()
{
	global $print_service_log_start_time;
	global $task_exec_time;

	echo PHP_EOL . PHP_EOL;
	echo 'Task Summary' . PHP_EOL;
	echo 'Start time: ' . PHP_EOL;
	echo 'TW  ' . date('Y/m/d H:i:s', $print_service_log_start_time) . PHP_EOL . PHP_EOL;
	
	// print task execution time 
	foreach ($task_exec_time as $key => $value)
	{
		echo "$key:\t\t $value seconds" . PHP_EOL;		
	}	
	
	echo PHP_EOL . 'Run time: ' . (get_taiwan_time() - $print_service_log_start_time) . ' seconds ' . PHP_EOL;
	echo '=========================================' . PHP_EOL . PHP_EOL . PHP_EOL;
}

// This lock class is used to prevent multiple entries of service scripts.
class service_lock {
   private $_lock_str;
   private $_fp;
   
   public function __construct($str ) 
   {
      $this->_lock_str = $str;
      $this->_fp = false;
   }

   public function lock( ) 
   {
	   $fname = $this->_lock_str . '.lock';
		$this->_fp = fopen($fname, "w+");

		if (flock($this->_fp, LOCK_EX | LOCK_NB)) 
		{ 
			// do an exclusive lock
    		return true;
		} 
		else 
		{
    		fclose($this->_fp);
    		$this->_fp = false;
    		return false;
    	}   
	}
   
   // this will be called automatically at the end of scope
   public function __destruct() 
   {
	   if ($this->_fp != false)
	   {
		   flock($this->_fp, LOCK_UN); // release the lock
		   fclose($this->_fp);
	   }
   }
}

// add a record which contains a user's query action for another user.
function add_query_user_record($user_id, $query_user_id)
{
	global $db;
	
	// user id smaller than 52 is a search engine bot.
	if ($user_id < 52)
		return;
		
	// insert to sv_query_user_record 
	$sql = 'INSERT IGNORE INTO sv_query_user_record (user_id, query_user_id, time) VALUES (' . $user_id . ' ,' .  $query_user_id . ' ,' . get_taiwan_time() . ')';
    $db->sql_query($sql);
}

function username_exist($name)
{
	global $db;
	
	$sql 	= 'SELECT * FROM phpbb_users WHERE username="' . $name . '"';
	$result = $db->sql_query_limit($sql, 1); 
	if ($db->sql_fetchrow($result))
		return true;
	else
		return false;	
}


// Get the price on the last trading day. 
function get_refered_price($corp_id)
{
	global	$db;
	
	static $price_array = array();
	
	if (isset($price_array[$corp_id]))
		return $price_array[$corp_id];
	
	$query		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC'; 
	$result		= $db->sql_query_limit($query, 1);
	if ($row = $db->sql_fetchrow($result))
	{
		$price_array[$corp_id] = (float)$row['price'];
		
		return (float)$row['price'];
	}
		
	return 0.0;
}

// get corp. chinese name.
function get_corp_chinese_name($corp_id)
{
	global	$db;
	
	if (is_valid_corp_id($corp_id) == false)
	{
		return '';		
	}
	
	$sql 	= 'SELECT * FROM sv_corp_info WHERE corp_id="' . $corp_id . '"';
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		$chinese_corp_name = utf8_normalize_nfc($row['chinese_name']);
		return $chinese_corp_name;
	}
	
	return '';
}

function get_username($user_id)
{
	global	$db;
	
	if ($user_id == '')
	{
		return '';		
	}
	
	$sql 	= 'SELECT * FROM ' . USERS_TABLE . ' WHERE user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		return $row['username'];		
	}
	
	return '';
}

// get all corp info from TWSE and OTC. 
function get_all_corp_info($insert_directly = false)
{
	global	$db;
	
	$keyword 		= '股票代碼選擇';
	

	// insert all corp info.
	$corp_info = ' ';
	$max_cat = 37;

	// get corp list from TWSE.
	for ($i = 1; $i <= $max_cat; ++$i)
	{
		// url is from http://www.twse.com.tw/ch/trading/inc/STKCHOICE/data_Top.htm
		if ($i < 10)
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK0$i.php";
		}		
		else if ($i == $max_cat - 1) // Add for ETF. 
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK0099P.php";
		}
		else if ($i == $max_cat) // Add for TDR. 
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK9299.php";
		}
		else
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK$i.php";
		}
	
		$raw_file = file_get_contents($url);
	
		if ($raw_file == false)
		{
			//echo "error to load $url \n";
			continue;
		}
		
		$content	= strip_tags($raw_file);
		$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
	
		$begin		= strpos($content, $keyword); 
		if ($begin === 0 )
		{
			echo "cannot find the key word in url:$url\n";
    		continue;
		}
	
		$begin 		+= strlen($keyword);
		$corp_info	.= (' ' . trim(str_replace('&nbsp;', ' ', substr($content, $begin))));
	}
	

	$keyword_OTC 	= '的支援';
	
	// get corp list from OTC.
	$url = "http://www.otc.org.tw//ch/inc/stksum.php";
		
	$raw_file = file_get_contents($url);
	if ($raw_file == false)
	{
		echo "fail to get url:$url\n";
		$message = "Fail to get corp list from OTC: $url";
	   	mail( "stockvirtual.info@gmail.com", "Fail to get corp list from OTC", $message, "From: " );
	    echo $message;
	}

	$content	= strip_tags($raw_file);
//	$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	
	//echo "content :$content \n";
		
	$begin		= strpos($content, $keyword_OTC); 
	//echo "begin :$begin \n";
	if ($begin != 0 )
	{
		// keyword found. 
		$begin 		+= strlen($keyword_OTC);		
		$corp_info	.= ("\n\t" . trim(str_replace('&nbsp;', ' ', substr($content, $begin))));		
	}
	
	//echo "corp info:$corp_info \n";
	
	// From 2014, the performance on EC2 is better, so we don't need this anymore. 
	// Connect to DB again. 
	// We may need to re-connect SQL server as getting corp info from TWSE/OTC takes much longer time than expected.
//	if (isset($dbpasswd))
//	{
//		echo "dbpasswd set, re-connect SQL server \n";
//		$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, defined('PHPBB_DB_NEW_LINK') ? PHPBB_DB_NEW_LINK : false);
//	}

	$tok = strtok($corp_info, " \t\r\n");
	$i = 0;
	while ($tok !== false) {
    	//echo "Token $i = $tok\n";

	    if ($i % 2 == 0)
    	    $corp_id = $tok;
	    if ($i % 2 == 1)
	    {        
    	    $chinese_name = $tok;
        
    	    if (is_valid_corp_id($corp_id))
    	    {
		        // check if the corp is already there.        
    		    $sql		= 'SELECT * FROM sv_corp_info WHERE corp_id="' . $corp_id . '" '; 
    		    //echo $sql . PHP_EOL;
	        	$result = $db->sql_query_limit($sql, 1);	      
	    	    if ($db->sql_fetchrow($result) == false)
    	    	{
	    	    	if ($insert_directly)
	        		{
		        		// insert to sv_corp_info
	        			$sql = 'INSERT INTO sv_corp_info (corp_id, chinese_name) VALUES ("' . $corp_id . '" ,"' .  $chinese_name . '")';        	
    	    			echo $sql . PHP_EOL;
	    	    		$db->sql_query($sql);
	    	    		
	    	    		// send out some notification. 		        		
		        		$message = "Added new corp: $corp_id , $chinese_name . Check here: http://tw.stock.yahoo.com/q/q?s=$corp_id";
	    	    		mail( "stockvirtual.info@gmail.com", "get_all_corp_info service", $message, "From: " );
	    	    		echo $message;
        			}
        			else
	        		{
		        		// send out some notification. 		        		
		        		$message = "Possible new corp: $corp_id , $chinese_name . Check here: http://tw.stock.yahoo.com/q/q?s=$corp_id";
	    	    		mail( "stockvirtual.info@gmail.com", "get_all_corp_info service", $message, "From: " );
	    	    		echo $message;
        			}		
        		}
    		}
    	}
        
	    $tok = strtok(" \t\r\n");
    	++$i;
	}
	
	if ($i == 0)
	{
		// we cannot find any corp name, send a warning
		$message = "Fail to get corp list from OTC: $url";
	   	mail( "stockvirtual.info@gmail.com", "Fail to get corp list from OTC", $message, "From: " );
	    echo $message;
	}
	
}

// We may need to use this work around as the MySQL is older than 5.1.
function get_correct_utf8_mysql_string($s)
{
    if(empty($s)) return $s;
    $s = preg_match_all("#[\x09\x0A\x0D\x20-\x7E]|
[\xC2-\xDF][\x80-\xBF]|
\xE0[\xA0-\xBF][\x80-\xBF]|
[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|
\xED[\x80-\x9F][\x80-\xBF]#x", $s, $m );
    return implode("",$m[0]);
} 

// Check if the user is a newbie. 
function is_newbie($user_id)
{
	if ($user_id == ANONYMOUS)
		return true;
		
	global $db;
	
	$sql	= 'SELECT * FROM sv_transaction WHERE user_id=' . $user_id; 
	$result = $db->sql_query_limit($sql, 1); 		
	if ($db->sql_fetchrow($result))
	{
		return false;
	}
	
	return true;	
}

function is_trading_day($time) 
{	
	// Need another checking for trading day , from http://www.twse.com.tw/ch/trading/trading_days.php 
	// ...		
			
	// Make a build-in non-trading day table according to the official schedule.
	// 1: no trading day
	// 2: trading compensation day
	$non_trading_day = array(
						'20150218' => 1,
						'20150219' => 1,
						'20150220' => 1,
						'20150223' => 1,
						'20150227' => 1,
						'20150403' => 1,
						'20150406' => 1,
						'20150501' => 1,
						'20150619' => 1,
						'20150928' => 1,
						'20151009' => 1,
	
						'20110404' => 1,
						'20110405' => 1,
						'20110606' => 1,
						'20110912' => 1,
						'20111010' => 1,
						'20120120' => 1,
						'20120121' => 1,
						'20120122' => 1,
						'20120123' => 1,
						'20120124' => 1,
						'20120125' => 1,
						'20120126' => 1,
						'20120127' => 1,
						'20120128' => 1,
						'20120129' => 1,
						'20120227' => 1,
						'20120228' => 1,
						'20120303' => 2,
						'20120404' => 1,
						'20120501' => 1,
						'20121010' => 1,
						'20121222' => 2,
						'20121231' => 1,
						
						'20130101' => 1,
						'20130207' => 1,
						'20130208' => 1,
						'20130209' => 1,
						'20130210' => 1,
						'20130211' => 1,
						'20130212' => 1,
						'20130213' => 1,
						'20130214' => 1,
						'20130215' => 1,						
						'20130223' => 2,
						'20130228' => 1,												
						'20130404' => 1,
						'20130405' => 1,
						'20130501' => 1,
						'20130612' => 1,
						'20130914' => 2,
						'20130919' => 1,
						'20130920' => 1,
						'20131010' => 1,
						
						'20140101' => 1,
						'20140128' => 1,
						'20140129' => 1,
						'20140130' => 1,
						'20140131' => 1,
						'20140201' => 1,
						'20140202' => 1,
						'20140203' => 1,
						'20140204' => 1,
						'20140228' => 1,
						'20140404' => 1,
						'20140405' => 1,
						'20140501' => 1,
						'20140602' => 1,
						'20140908' => 1,
						'20141010' => 1,						
						
						);
	
	$d = date('Ymd', $time);
	
	// The schedule is with higher priority than weekday checking because sometimes there may be compensation trading day.
	if ($non_trading_day[$d] == 1)
		return false;
	else if ($non_trading_day[$d] == 2)
		return true;
	
	// Trading time: Mon~Fri, 9:00~13:30
	if (date(w, $time) == 0 || date(w, $time) == 6)
		return false;  	
		
	return true;
}
		
function is_trading_time($time) 
{
	// Trading time: Mon~Fri, 9:00~13:30
	if ( date(G, $time) < 9 || date(G, $time) > 13)
		return false;
	if (date(G, $time) == 13 && date(i, $time) > 30)
		return false;  

	return true;
}

// Get the latest finished trading date in Ymd format. 
function get_latest_trading_date($time) 
{	
	if ( (int)date(G, $time) < 9)
	{
		$time -= 12*60*60;
	}
	
	while (true)
	{
		if (is_trading_day($time))
		{
			break;
		}
		else
		{
			$time -= 24*60*60;
		}
	}

	// return the date in Ymd format, ex: 20101231. This date format is used in SV database.
	return get_date_from_time($time);
}

// Get the start time of the current trading day(today). 
// For simplicity, just use the current time minus 12 hours. 
function get_current_trading_day_start_time($time) 
{
	return $time - 12*60*60;
}

// Get the capital reduction information.
function get_capital_reduction_info()
{
	global $db;
	
	echo "get_capital_reduction_info:\r\n";
	
	$url = "http://www.twse.com.tw/ch/trading/exchange/TWTAVU/TWTAVU.php";
	
	$raw_file = file_get_contents($url);
    if ($raw_file == false)
    {
	    $message = "  $url ";
		mail( "stockvirtual.info@gmail.com", "Fail to get Capital Reduction Info ", $message, "From: " );
    	return;
	}
	
    $content	= iconv("BIG-5", "UTF-8//IGNORE", $raw_file);	// convert the web page from BIG-5 to UTF-8.    
    $begin		= strpos($content, '參考價試算'); 
    $end		= strpos($content, '公式：減資換股率');
    
    if ($begin === 0 || $end === 0)
    	return '';
	
    $begin	= $begin + strlen('參考價試算');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);    
    
    // now $str contains several window.open() calls and every entry of capital reduction information has 2 window.open() call. 
    // we will get the url of the 1st call for each entry, then we can get the detail info.
    while(true)
    {
	    $begin 	= strpos($str, "window.open('"); 
	    $end 	= strpos($str, "',null,"); 
	    
	    if ($begin == 0 || $end == 0)
	    {
	    	break;
    	}
	    	
	    $begin	= $begin + strlen("window.open('");
	    
	    $url 	= 'http://www.twse.com.tw/ch/trading/exchange/TWTAVU/' . substr($str, $begin , $end - $begin);
	    //echo "\nurl: $url\n";
	    
	    // get detail info.
	    $raw_file = file_get_contents($url);
    	if ($raw_file)
    	{
    		$content	= strip_tags($raw_file);
    		$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    		
    		//echo $content;
    		//echo '=======================\n\n';
    		
    		// corp id
    		$begin 	= strpos($content, '股票代號：'); 
	    	$end 	= strpos($content, '股票名稱：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('股票代號：');
	    		$corp_id = substr($content, $begin, $end - $begin);
	    		
	    		//echo "corp_id:$corp_id\n";
    		}
    		
    		// scheduled date
    		$begin 	= strpos($content, '停止買賣日期：'); 
	    	$end 	= strpos($content, '每壹仟股換發新股票：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('停止買賣日期：');
	    		$scheduled_date = substr($content, $begin, $end - $begin);	    		
    		}
    		
    		// share after change
    		$begin 	= strpos($content, '每壹仟股換發新股票：'); 
	    	$end 	= strpos($content, '股每股退還股款：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('每壹仟股換發新股票：');
	    		$share_after_change = substr($content, $begin, $end - $begin);		
    		}
    		
    		// cash after change
    		$begin 	= strpos($content, '股每股退還股款：'); 
	    	$end 	= strpos($content, '元/股減資並(有償)現金增資：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('股每股退還股款：');
	    		$cash_after_change = substr($content, $begin, $end - $begin);
    		}
    		
    		// set basic share unit to 1000*1000 for better precision. 
    		$share_unit 		= 1000000;
    		$cash_after_change	= (int)((float)$cash_after_change * 1000);
    		$share_after_change	= (int)((float)$share_after_change * 1000);
    		
    		echo "cash_after_change:$cash_after_change\n";
    		echo "share_after_change:$share_after_change\n";
    		
    		// update the date to 20xx
    		$y = (int)(substr($scheduled_date, 0, 3)) + 1911;
    		$d = $y . substr($scheduled_date, 4, 2) . substr($scheduled_date, 7, 2);
    		$scheduled_date = $d;
    		echo "scheduled_date:$scheduled_date\n";
    		
    		if (is_valid_date($scheduled_date) == false)
    		{
	    		$message = "corp id:$corp_id, scheduled_date:$scheduled_date";
	    		mail( "stockvirtual.info@gmail.com", "Invalid Date - Capital Reduction Info ", $message, "From: " );
    		}
    	
    		if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
			{
				$corp_id_after_change = $corp_id;
				$corp_id_after_change_2 = "";
				$share_after_change_2 = 0;
				$corp_id_after_change_3 = "";
				$share_after_change_3 = 0;
		
				$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
				$result	= $db->sql_query_limit($sql, 1); 
				if( ! ($db->sql_fetchrow($result)))
				{
					$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
					echo "$sql\r\n\r\n";
					$result = $db->sql_query($sql);
				}
			}
		}
	    
	    // skip 2nd window.open() call
	    $end2 	= strpos($str, "試算"); 
	    $str = substr($str, $end2 + 4);
    }
    
    echo "get_capital_reduction_info - end\r\n\r\n\r\n";
}

// Define our own str_getcsv as the PHP on StockVirtual doesn't support this function.
function sv_str_getcsv($input, $delimiter=',', $enclosure='"', $escape=null, $eol=null) 
{
	$temp=fopen("php://memory", "rw");
	fwrite($temp, $input);
	fseek($temp, 0);
	$r = array();
	while (($data = fgetcsv($temp, 84096, $delimiter, $enclosure)) !== false) 
	{
    	$r[] = $data;
	}
	fclose($temp);
	return $r;
}

// get dividend info from OTC.
function get_dividend_from_otc()
{
	global $db;
	
	echo "get_dividend_from_otc - begin\n";
	$raw_file = file_get_contents("http://www.otc.org.tw/web/stock/exright/preAnnounce/PrePost_download.php?l=en-us&s=0,asc,0");
    if ($raw_file == false)
    {
	    $message = "Failed to get dividend from OTC : http://www.otc.org.tw/web/stock/exright/preAnnounce/PrePost_download.php?l=en-us&s=0,asc,0 ";
	    mail( "stockvirtual.info@gmail.com", "Fail to get OTC dividend ", $message, "From: " );
    	return false;
	}

    $csv 		= sv_str_getcsv ($raw_file, "\n");
    
    foreach($csv as &$row)
    {
	    $row = sv_str_getcsv($row[0]);
	    if (sizeof($row[0]) != 7)
	    	continue;
		if (is_valid_corp_id($row[0][1]) == false)
			continue;

		$corp_id 			= $row[0][1];
    	$share_unit 		= TRANSACTION_UNIT;
    	$cash_after_change	= (float)$row[0][6] * TRANSACTION_UNIT;
    	$share_after_change	= (float)$row[0][2] * TRANSACTION_UNIT + 1000;
    	$scheduled_date		= trim($row[0][0]);

    	if ($cash_after_change == 0 &&  $share_after_change == TRANSACTION_UNIT)
    		continue;
    			
    	echo "corp_id: $corp_id\n";
    	echo "cash_after_change:$cash_after_change\n";
    	echo "share_after_change:$share_after_change\n";
    	echo "scheduled_date:$scheduled_date\n";
    	
    	// check if the date is valid
    	if (is_valid_date($scheduled_date) == false)
    	{
	    	$message = "corp id:$corp_id, scheduled_date:$scheduled_date";
	    	mail( "stockvirtual.info@gmail.com", "Invalid Date - OTC Dividend", $message, "From: " );
    	}
    		
    	if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
		{
			$corp_id_after_change = $corp_id;
			$corp_id_after_change_2 = "";
			$share_after_change_2 = 0;
			$corp_id_after_change_3 = "";
			$share_after_change_3 = 0;
		
			$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
			$result	= $db->sql_query_limit($sql, 1); 
			if( ! ($db->sql_fetchrow($result)))
			{
				$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
				echo "$sql\r\n\r\n";
				$result = $db->sql_query($sql);
				
				$message = 'OTC Corp ' . $corp_id . ' ( ' . get_corp_chinese_name($corp_id) . ' ) ' . ' dividend info: cash_after_change: ' . $cash_after_change . ' share_after_change: ' . $share_after_change . ' scheduled_date: ' . $scheduled_date . ' Check here: http://www.gretai.org.tw/ch/stock/exright/preAnnounce/prepost.php#';
				mail( "stockvirtual.info@gmail.com", "Dividend", $message, "From: " );
			}
		}
	}   
    
	echo "get_dividend_from_otc - end\n\n";
}

// get dividend info from TWSE.
function get_dividend_from_twse()
{
	global $db;
	
	echo "get_dividend_from_twse - begin\n";
	
	$date = get_date_from_time(get_taiwan_time());
	
	// Prepare the date format for querying to TWSE. 
	$y = get_year_from_date($date);
	$m = get_month_from_date($date);
	$d = get_day_from_date($date);	
	$y = (int)($y) - 1911;
	
	$url = "http://www.twse.com.tw/en/trading/exchange/TWT48U/TWT48U_PD.php?chk_date=$y/$m/$d&Sort_kind=DATE&type=csv";
	$raw_file = file_get_contents($url);
    if ($raw_file == false)
    {
	    $message = "Failed to get dividend from TWSE : $url";
	    mail( "stockvirtual.info@gmail.com", "Fail to get TWSE dividend ", $message, "From: " );
    	return false;
	}

    $csv 		= sv_str_getcsv ($raw_file, "\n");
    
    foreach($csv as &$row)
    {
	    $row = sv_str_getcsv($row[0]);    
	    
	    if (sizeof($row[0]) != 6)
	    {
		    //echo "size not 6\n";
	    	continue;
    	}
		if (is_valid_corp_id($row[0][1]) == false)
		{
			//echo "not valid corp id\n";
			continue;
		}

		$corp_id 			= $row[0][1];
    	$share_unit 		= TRANSACTION_UNIT;
    	$cash_after_change	= (float)$row[0][5]*TRANSACTION_UNIT;
    	$share_after_change	= (float)$row[0][2] * TRANSACTION_UNIT + 1000;
    	$scheduled_date		= trim($row[0][0]);

    	if ($cash_after_change == 0 &&  $share_after_change == TRANSACTION_UNIT)
    		continue;
    			
    	echo "corp_id: $corp_id\n";
    	echo "cash_after_change:$cash_after_change\n";
    	echo "share_after_change:$share_after_change\n";
    		
    	$y = substr($scheduled_date, 0, 4);
    	$d = $y . substr($scheduled_date, 5, 2) . substr($scheduled_date, 8, 2);
    	$scheduled_date = $d;
    	echo "scheduled_date:$scheduled_date\n";
    		
    	if (is_valid_date($scheduled_date) == false)
    	{
	    	$message = "corp id:$corp_id, scheduled_date:$scheduled_date";
	    	mail( "stockvirtual.info@gmail.com", "Invalid Date - TWSE Dividend", $message, "From: " );
    	}
    	
    	if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
		{
			$corp_id_after_change = $corp_id;
			$corp_id_after_change_2 = "";
			$share_after_change_2 = 0;
			$corp_id_after_change_3 = "";
			$share_after_change_3 = 0;
		
			$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
			$result	= $db->sql_query_limit($sql, 1); 
			if( ! ($db->sql_fetchrow($result)))
			{
				$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
				echo "$sql\r\n\r\n";
				$result = $db->sql_query($sql);
				
				$message = 'TWSE Corp ' . $corp_id . ' ( ' . get_corp_chinese_name($corp_id) . ' ) ' . ' dividend info: cash_after_change: ' . $cash_after_change . ' share_after_change: ' . $share_after_change . ' scheduled_date: ' . $scheduled_date . '  CHECK HERE: http://www.twse.com.tw/ch/trading/exchange/TWT48U/TWT48U.php  ';
				mail( "stockvirtual.info@gmail.com", "Dividend", $message, "From: " );
			}
		}
	}   
    
	echo "get_dividend_from_twse - end\n\n";
}


// Get the TAIEX index of a specific date. 
function get_taiex($date, $query_twse = false)
{
	global $db;
	
	$len = strlen($date);
	if ($len != 8)
		return '';
		
	// search from database.
	$query	= 'select * from sv_taiex where date="' . $date . '"'; 
	$result	= $db->sql_query_limit($query, 1); 
	if( $row = $db->sql_fetchrow($result))
	{
		return $row['closing_index'];
	}
	
	// If we are not required to query TWSE, return null string as the database don't have the data.
	if ($query_twse == false)
		return '';
		
	// Query from TWSE. 
	$y = get_year_from_date($date);
	$m = get_month_from_date($date);
	$d = get_day_from_date($date);
	
	$y = (int)($y) - 1911;
	$url = "http://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php?myear=$y&mmon=$m";
	//echo $url;
	
    $raw_file = file_get_contents($url);
    if ($raw_file == false)
    	return '';
	
	$content	= strip_tags($raw_file);
    $content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    $begin		= strpos($content, '發行量加權股價指數歷史資料日期開盤指數最高指數最低指數收盤指數'); 
    $end		= strpos($content, 'function adjustFooterPosition');
    if ($begin === 0 || $end === 0)
    	return '';
	
    $begin	= $begin + strlen('發行量加權股價指數歷史資料日期開盤指數最高指數最低指數收盤指數');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);
    
	$tok = strtok($str, " ");
	$i = 0;
	while ($tok !== false) 
	{  	    	
    	if ($i % 5 == 0)		// date
    		$q_date = $tok;
    	if ($i % 5 == 4)		// index
    	{
    		$q_index = $tok;    		
    		
			$y = substr($q_date, 0, strlen($q_date) -6);
			$m = substr($q_date, strlen($q_date) - 5, 2);
			$d = substr($q_date, strlen($q_date) - 2, 2);
    		
    		$y = (int)($y) + 1911;
    		$i_date = '' . $y . $m . $d;
    		
    		$q_index = ereg_replace("[^0-9.]", "", $q_index); 			
    		$i_closing_index = (float)($q_index);
    		
    		// insert the index record into the database.
    		$sql = 'INSERT IGNORE INTO sv_taiex SET date="' . $i_date . '", closing_index=' . $i_closing_index;
    		//echo "    $sql\n";
    		$result	= $db->sql_query($sql); 
    		
    		if ($date == $i_date)
    			return $i_closing_index;
		}
    	
    	$tok = strtok(" ");
    	++$i;
	}
	
    return '';
		
}

// Get the price, volume and related info of a stock.
function get_stock_price_volume($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{	
	if (get_stock_price_volume_from_twse($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit))
		return true;
	else
		return get_stock_price_volume_from_yahoo($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit);
}

// Get info from TWSE Market Information System.
function get_stock_price_volume_from_twse($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{
	if (is_valid_corp_id($corp_id) == false)
		return false;

	$raw_file = file_get_contents("http://mis.twse.com.tw/data/$corp_id.csv");
    if ($raw_file == false)
    	return false;
	
    $csv 	= sv_str_getcsv ($raw_file);

    $price 		= (float)($csv[0][8]);
    $volume 	= ereg_replace("[^0-9]", "", $csv[0][9]);					// 累積成交數量
    $current_short_term_volume 	= ereg_replace("[^0-9]", "", $csv[0][10]);	// 當盤成交數量
    
    if ($price < 0.01)
    {
	    echo "invalid price - $price \n";
	    return false;	    
    }
    
    // Check 漲停, 跌停
    $reach_surged_limit = false;
    $reach_decline_limit = false;
    if ($csv[0][3] == $csv[0][8])
    	$reach_surged_limit = true;
    if ($csv[0][4] == $csv[0][8])
    	$reach_decline_limit = true;

    return true;
}

// Get info from Yahoo stock.
function get_stock_price_volume_from_yahoo($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{
	if (is_valid_corp_id($corp_id) == false)
		return false;
		
	echo "get_stock_price_volume_from_yahoo $corp_id\n";
	
	// get stock price/volume. 
    $raw_file = file_get_contents("http://tw.stock.yahoo.com/q/q?s=$corp_id");
    if ($raw_file == false)
    	return false;
    	
    $content	= strip_tags($raw_file);    
    $content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    $begin		= strpos($content, '加到投資組合'); 
    $end		= strpos($content, '成交明細技術');
    if ($begin === 0 || $end === 0)
    	return false;
    	
    $begin	= $begin + strlen('加到投資組合');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);
	$tok = strtok($str, " ");
	$i = 0;
	while ($tok !== false) 
	{  	    	
    	if ($i == 1)		// index of current price
    		$price = (float)$tok;
    	if ($i == 5)		// index of volume today
    		$volume = $tok;
    	
    	$tok = strtok(" ");
    	++$i;
	}
	
	// leave only numbers. 
	$volume = ereg_replace("[^0-9]", "", $volume); 
	$volume = (int)$volume;
	
	// Yahoo doesn't provide current short term volume, so we just use volume.
	$current_short_term_volume = $volume;
	
	// error checking.
	if ($price < 0.01)
	{
		echo "invalid price - $price \n";
		return false;
	}
	
	echo "price:$price volume:$volume";
	
	return true;
}

function update_all_stock_price()
{
	global $db, $time, $price, $volume;
	
	$latest_trading_date = get_latest_trading_date($time);			
	
	$query	= "select * from sv_corp_info"; 
	$result	= $db->sql_query($query) or die("Failed Query of " . $query); 
	while( $thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id = $thisrow['corp_id'];
    	echo "corp_id: $corp_id\n";
    	
    	// query if the record of (corp, date, price) is already in. 
    	$query2		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" AND date=' . $latest_trading_date; 
		$result2	= $db->sql_query_limit($query2, 1);
		if ($db->sql_fetchrow($result2))
		{
			echo "already exist: corp_id:$corp_id date:$latest_trading_date \n";
			continue;
		}
    	    	
    	// Get price, volume 
		$price = $volume = $current_short_term_volume = 0;
		$reach_surged_limit = $reach_decline_limit = false;
		
		// wait for 0.5 seconds, 1 second = 1000000
		usleep(500000);
		
		if (get_stock_price_volume($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit) == false)
		{			
			echo " fail to get price/volume \n";
			// send out some notification. 
		    $message = "fail to get price/volume: $corp_id, "  . get_corp_chinese_name($corp_id) . '  time: ' . date('Y/m/d H:i:s', $time);
		    $message .= PHP_EOL . 'click here to check: http://tw.stock.yahoo.com/q/q?s=' . $corp_id . ' or http://mis.twse.com.tw/stock_best5.html?stockId=' . $corp_id;
	    	mail( "stockvirtual.info@gmail.com", "update_all_stock_price service", $message, "From: " );
			continue;
		}
		echo "corp_id:$corp_id price:$price volume:$volume \n";
		
		$sql2 = 'INSERT INTO sv_stock_price (corp_id, date, time, price, volume) VALUES ("' . $corp_id . '", ' . "$latest_trading_date, $time, $price, $volume)";
		//echo "$sql2 \n";
		$db->sql_query($sql2);
	}
}


//
//	Calculate moving average of stock prices for several periods
//
//	quick_update
//				true (default):	only calculate MA for 5 and 10 day period
//				false:			calculate MA for 5, 10, 20, 30 , 60 and 120 day period
//
//	ignore_previous_record
//				true:				ignore previous MA records and fetch all relevant prices to calculate MA
//				false (default):	calculate MA based on the MA records 
//
//
function update_all_stock_moving_average_price($quick_update = true, $ignore_previous_record = false)
{
	global $db, $time;
	
	echo "update_all_stock_moving_average_price: quick_update:$quick_update, ignore_previous_record:$ignore_previous_record\n";
	
	$latest_trading_date = get_latest_trading_date($time);			
	// we can only update MA in short periods( MA5, MA10, MA20) on a daily basis to save execution time, then we will update all MA weekly because we will have plenty time during weekends. 
	// only fetch 11 price records if quick_update
	if ($quick_update)
	{
		$period		= array(5, 10, 20);		
		$max_record	= 11;
	}
	else
	{
		// We calculated to MA250 once on EC2 and it took 8 hours to complete. 
		// So we only calculate to MA120 here. 
		$period		= array(5, 10, 20, 30, 60, 120);		
		$max_record	= 121;		
	}
	
	// 10 prices are included in the list
	$recent_prices_max 		= 10;
	
	
	$query	= "select * from sv_corp_info"; 
	$result	= $db->sql_query($query) or die("Failed Query of " . $query); 
	while( $thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id = $thisrow['corp_id'];
    	echo "corp_id: $corp_id\n";
    	
    	// query if the record of (corp, date, price) is already in. 
    	$query2		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" AND date=' . $latest_trading_date; 
		$result2	= $db->sql_query_limit($query2, 1);
		if (($thisrow2 = $db->sql_fetchrow($result2)) == false)
		{
			echo "no latest price update: corp_id:$corp_id date:$latest_trading_date \n";
			
			// there must be something wrong, so clear the recent prices. 
			$sql2 = 'UPDATE sv_corp_info SET ma5=0.0, ma10=0.0, ma20=0.0, ma30=0.0, recent_prices="" WHERE corp_id="' . $corp_id . '"';
//			echo "$sql2 \n";
			$db->sql_query($sql2);		
			continue;
		}		
		
		$price_to_add 	= $thisrow2['price'];						
		$MA5 			= $thisrow['ma5'];
		$recent_prices	= $thisrow['recent_prices'];	// include recent 10 prices
		
		// reset recent prices for each corp 
		unset($recent_prices_array);
		unset($price_list);
		
		$recent_prices_array 	= array();		
		
		echo "price_to_add:$price_to_add\n";
		
		
//		echo "reset recent prices for each corp \n";
//		print_r($recent_prices_array);
//		echo "\n";
			
		$MA 	= array();
		$s_time	= microtime(true);
		
		// if the moving average is calculated before
		if ($ignore_previous_record == false && $MA5 != 0.0)
		{
			$MA[0] = $thisrow['ma5'];				$MA[1] = $thisrow['ma10'];
			$MA[2] = $thisrow['ma20'];				$MA[3] = $thisrow['ma30'];
			$MA[4] = $thisrow['ma60'];				$MA[5] = $thisrow['ma120'];			
			
			foreach ($period as $idx => $p)
			{					
				// in order to force MYSQL to do late row lookup, this special SQL statement is used. 
				$p_th = $p + 1;
				$sql2	 = '
							SELECT  t.* 
							FROM    (
							        SELECT  stock_price_id
							        FROM    sv_stock_price 
							        WHERE corp_id="' .$corp_id . '" ' . "
							        ORDER BY stock_price_id desc LIMIT $p_th, 1  
							        ) q
							JOIN    sv_stock_price t
							ON      t.stock_price_id = q.stock_price_id
							";

//				echo "$sql2 \n";
				$result2 = $db->sql_query($sql2); 
				if ($thisrow2 = $db->sql_fetchrow($result2))
				{
					$price_to_remove = $thisrow2['price'];
					$MA[$idx] = ($MA[$idx] * $p - $price_to_remove + $price_to_add) / $p;					
				}
			}
			
			// update recent price list
			$fetch_all_recent_prices = true;
			
			
			if ($recent_prices != '')
			{
				echo "recent_prices not empty - $recent_prices\n";
				
				$price_list = explode(',', $recent_prices);			
				
//				echo "price_list from the string:\n";
//				print_r($price_list);
//				echo "\n";						
										
				if (count($price_list) >= $recent_prices_max)
				{
					for($i = 1; $i < $recent_prices_max; ++$i)
					{
						$recent_prices_array[] = $price_list[$i];						
					}
					
//					print_r($recent_prices_array);
//					echo "\n";		
					
					// add the latest price
					$recent_prices_array[] 	= $price_to_add;
															
					$fetch_all_recent_prices = false;
				}
			}
			
			if($fetch_all_recent_prices)
			{
				echo "recent_prices is empty or not enough, we are fetching all recent prices\n";
				
				// the price list is empty, so get from DB
				$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
				$result3 	= $db->sql_query_limit($sql3, $recent_prices_max); 	
				while ($thisrow3 = $db->sql_fetchrow($result3))
				{
					$recent_prices_array[] = $thisrow3['price'];
				}
				
//				echo "After fectchin all prices, recent_prices_array :\n";
//				print_r($recent_prices_array);
//				echo "\n";
			}
		}
		else
		{	
			// calculate MA from the very beginning 						
			$sql2		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
			$result2 	= $db->sql_query_limit($sql2, $max_record); 
		
			// iterate to get sum for every period
			$i 		= 0;
			$sum 	= array();
			while ($thisrow2 = $db->sql_fetchrow($result2))
			{
				$price = $thisrow2['price'];			
				
				foreach ($period as $idx => $p)
				{
					if ($i < $p)
						$sum[$idx] += $price;
				}	
			
				// update recent price list
				if ($i < $recent_prices_max)
					$recent_prices_array[] = $price;
							
				++$i;			
			}
			
			// divide by period length to get MA
			foreach ($period as $idx => $p)
			{
				if ($i > $p)
					$MA[$idx] = $sum[$idx] / $p;	
				else
					$MA[$idx] = $sum[$idx] / $i;
			}			
		}
		
		$e_time	= microtime(true) - $s_time;
		echo "it takes $e_time seconds\n";
		
		$recent_prices = implode(", ", $recent_prices_array);
		
		// update 
		if ($quick_update)
		{
			$sql2 = 'UPDATE sv_corp_info SET ma5=' . $MA[0] . ', ma10=' . $MA[1] . ', recent_prices="' . $recent_prices . '" WHERE corp_id="' . $corp_id . '"';
//			echo "$sql2 \n";
			$db->sql_query($sql2);		
		}
		else
		{
			$sql2 = 'UPDATE sv_corp_info SET ma5=' . $MA[0] . ', ma10=' . $MA[1] . ', ma20=' . $MA[2] . ', ma30=' . $MA[3] . ', ma60=' . $MA[4] . ', ma120=' . $MA[5] . ', recent_prices="' . $recent_prices . '" WHERE corp_id="' . $corp_id . '"';
//			echo "$sql2 \n";
			$db->sql_query($sql2);		
		}
	}
	
	echo "update_all_stock_moving_average_price - done\n";
}


// deal with all transaction and check if they can be done.
function deal_all_transaction($off_hour_trading = false)
{
	global $db, $time, $price, $volume;
	
	$current_trading_day_start_time = get_current_trading_day_start_time(get_taiwan_time()); 
		
	// get TWSE and OTC total value.
//	$total_value_in_twse_otc = get_TWSE_value() + get_OTC_value();
//	echo "Total value in TWSE/OTC: $total_value_in_twse_otc\n";
	
	// get StockVirtual total value today.
//	$query	= 'SELECT SUM(share*price) FROM sv_transaction WHERE deal_time > ' . $current_trading_day_start_time; 		
//	$result	= $db->sql_query($query);
//	$row	= $db->sql_fetchrow($result);
//	$total_value_in_sv = $row['SUM(share*price)'];
//	echo "Total value in StockVirtual: $total_value_in_sv\n";
//	$ratio = $total_value_in_sv * 100 / $total_value_in_twse_otc;
//	echo "ratio: $ratio %\n";
	
	if ($off_hour_trading == false)
		$sql	= "SELECT * FROM sv_transaction WHERE action='to_buy' OR action='to_sell' OR action='to_short_cover' OR action='to_short_sell' ORDER BY corp_id"; 
	else
		$sql	= "SELECT * FROM sv_transaction WHERE action='to_sell_off_hour'  ORDER BY corp_id"; 
	$result = $db->sql_query($sql); 
		
	$saved_corp_id = '';
		
	while ($thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id 		= $thisrow['corp_id'];
    	$transaction_id	= $thisrow['transaction_id'];    	
    	echo PHP_EOL . '---------------' . PHP_EOL;
    	echo "Checking transaction_id: $transaction_id  corp_id:$corp_id\n";

    	$user_id		= $thisrow['user_id'];
		$request_share	= $thisrow['share'];
		$request_price	= $thisrow['request_price'];
		$action			= $thisrow['action'];
		echo "user_id:$user_id request_share:$request_share request_price:$request_price action:$action \n";		  	
    	
    	if (is_valid_corp_id($corp_id) == false)
    	{
	    	echo "not valid corp id, continue to next one\n";
	    	// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="corp_id_not_valid"  WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
	    	continue;
    	}
    	
    	// For a different corp, get the latest price/volume info.
    	$price = $volume = $current_short_term_volume = 0;
    	$reach_surged_limit = $reach_decline_limit = false;
    	    	
    	sleep(1);
		if ($corp_id != $saved_corp_id && get_stock_price_volume($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit) == true)
		{
			$saved_corp_id 						= $corp_id;
			$saved_price 						= $price;
			$saved_volume						= $volume;			
			$saved_current_short_term_volume	= $current_short_term_volume;			
			$saved_reach_surged_limit			= $reach_surged_limit;
			$saved_reach_decline_limit			= $reach_decline_limit;							
			
			echo "saved_corp_id: $saved_corp_id  saved_price:$saved_price saved_volume: $saved_volume current_volume: $current_short_term_volume, surged_limit:$reach_surged_limit, decline limit: $reach_decline_limit" . PHP_EOL;
		}

		// Check if the latest corp price/volume info is saved.
		if ($corp_id != $saved_corp_id)
		{
			echo "error: cannot get price/volume info\n";
			// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="cannot_get_price"  WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);			
			continue;
		}		
		
		
		// Check if the dealed volume is big enough in current trading day.
		$query2	= 'SELECT SUM(share) FROM sv_transaction WHERE corp_id="' . $corp_id . '" AND deal_time > ' . $current_trading_day_start_time; 		
		$result2	= $db->sql_query($query2);
		$row2		= $db->sql_fetchrow($result2);
		
		$sum_dealed_share = (int)$row2['SUM(share)'];
		echo "sum_dealed_share: $sum_dealed_share \n";		
		
		$volume_limit = $saved_volume;
		
		// If the stock reaches surged or decline limit, the volume will restrict to current volume. 
		// In this case, most people cannot buy/sell the stocks. 
		if ((($action == 'to_buy' || $action == 'to_short_cover') && $saved_reach_surged_limit) ||
			(($action == 'to_sell'|| $action == 'to_short_sell') && $saved_reach_decline_limit))
		{	
			echo "surged or decline limit reached: saved_current_short_term_volume:$saved_current_short_term_volume,saved_volume:$saved_volume  " . PHP_EOL;
			// Limit the volume at the lower of 1% of total volume and the current volume.
			$volume_limit = $saved_current_short_term_volume;
			if ($volume_limit > $saved_volume / 100)
				$volume_limit = (int)($saved_volume / 100);
				
			echo "volume_limit:$volume_limit " . PHP_EOL;
		}
				
		// check if the volume limit is already reached
		if ($volume_limit * TRANSACTION_UNIT <= $sum_dealed_share)
		{
			echo "volume limit reached, sum_dealed_share:$sum_dealed_share, volume limit:$volume_limit " . PHP_EOL ;
			// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="volume_not_enough" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			continue;
		}
		// check if this transaction can be completed partially 
		else if ($volume_limit * TRANSACTION_UNIT < $request_share + $sum_dealed_share)
		{
			echo "this transaction can be completed partially request_share:$request_share, sum_dealed_share:$sum_dealed_share volume_limit:$volume_limit" . PHP_EOL;
			// to complete a transsaction partially, we do 2 steps:
			// 1. modify the share of this transaction to the allowed number, and complete it
			// 2. create another transaction with the remaining share number
			$allowed_share 		= $volume_limit * TRANSACTION_UNIT - $sum_dealed_share;
			$remaining_share 	= $request_share - $allowed_share;
			$request_share		= $allowed_share;
			
			$db->sql_transaction('begin');
			
			//modify the share of this transaction to the allowed number, and complete it
			$sql = 'UPDATE sv_transaction SET share=' . $allowed_share . ' WHERE transaction_id=' . $transaction_id;
			echo "$sql " . PHP_EOL;
			$db->sql_query($sql);
			
			// create another transaction with the remaining share number with proper check time/status as we already this new transaction will be blocked due to volume limit reached			
			$sql = 'INSERT INTO sv_transaction ' . $db->sql_build_array('INSERT', array(
							'user_id'		=> $user_id,
							'corp_id'		=> $corp_id,
							'action'		=> $action,
							'share'			=> $remaining_share,
							'price'			=> (float) 0.0,
							'request_price'	=> $request_price,
							'request_time'	=> $thisrow['request_time'],
							'note'			=> $note,
							'deal_time'		=> '',
							'check_time'	=> get_taiwan_time(),
							'check_status'	=> 'volume_not_enough',
							'note'			=> $thisrow['note'],
							)
						);
			echo "$sql " . PHP_EOL;
			$db->sql_query($sql);
			
			
			$db->sql_transaction('commit');
		}
		else if ($volume_limit * TRANSACTION_UNIT > $request_share + $sum_dealed_share)
		{
			// totally fine, just go ahead
			echo "totally fine, just go ahead. request_share:$request_share, sum_dealed_share:$sum_dealed_share, volume_limit:$volume_limit" . PHP_EOL;
			
		}
				
		echo "dealed volume is big enough, $saved_volume(1000 shares)" . PHP_EOL;
					
		// Delegation is in market price. 	
		if ($request_price == 0.0)
		{
			$dealed_price = $saved_price;
			echo "market price => $dealed_price" . PHP_EOL;					
		}
		else
		{			
			
			if (
				($request_price <= $saved_price &&
				($action == 'to_sell' || $action == 'to_sell_off_hour' || $action == 'to_short_sell' ))
				||
				($request_price >= $saved_price &&
				($action == 'to_buy' || $action == 'to_short_cover' ))
				)
			{
				// price meet	
				$dealed_price = $saved_price;
				echo "price requested ($request_price) meets market price ($dealed_price)" . PHP_EOL;
			}
			else 
			{
				// price not meet 
				echo "price requested not met, request_price:$request_price,  saved_price:$saved_price " . PHP_EOL;
				
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="price_not_met" WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				continue;			
			}			
		}
		
								
		// to buy
		if ($action == 'to_buy')
		{
			$needed_money = $dealed_price * $request_share;
			// add fee.
			$needed_money = $needed_money * (1 + SECURITY_TRANSACTION_FEE);
			echo "To buy, needed money: $needed_money \n";
						
			// Check if the user has enough money
			$query2	= "SELECT * FROM phpbb_users WHERE user_id=" . $user_id; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);
			
			$db->sql_freeresult($result2);
			
			$user_money = $row2['user_money'];
					
			echo "user money: $user_money\n";
					
			if ($user_money < $needed_money)
			{
				echo "Not enough money\n";
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="money_not_enough" WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				continue;
			}					
					
			$db->sql_transaction('begin');
						
			// Add the stock to the user
			$query2		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);	
			
			$db->sql_freeresult($result2);
								
			if ($row2)
			{
				// the user already has the stock 
				$share 	= (int)$row2['share'] + (int)$request_share;
					       		
	       		// calculate the average cost
	       		$avg_cost = ($dealed_price * $request_share + $row2['share'] * $row2['cost']) / ($request_share + $row2['share']);
	       		
	       		$sql 	= 'UPDATE sv_holding_stock SET share=' . $share . ', cost=' . $avg_cost . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       		$db->sql_query($sql);
	       		echo $sql . PHP_EOL;
			}
			else
			{
				$sql = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $corp_id . '", "' . $request_share . '", "' . $dealed_price . '")';
           		$db->sql_query($sql);
           		echo $sql . PHP_EOL;
       		}
						
			// Take the money from the user
			$sql = 'UPDATE phpbb_users SET user_money=user_money-' . $needed_money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
						
			// Update transaction status
			$sql = 'UPDATE sv_transaction SET action="buy", deal_time=' . (get_taiwan_time()) . ', price=' . $dealed_price . '  WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
						
			$db->sql_transaction('commit');
			echo "buy - done\n";
		}
		// to sell
		else if ($action == 'to_sell' || $action == 'to_sell_off_hour')		
		{
			// we should double confirm if this delegation is still not deleted by the user.
			$sql2		= "SELECT * FROM sv_transaction WHERE transaction_id=$transaction_id"; 
			$result2 	= $db->sql_query($sql2); 
			$row2 		= $db->sql_fetchrow($result2);
			
			$db->sql_freeresult($result2);
			
			// check if this delegation is already deleted.
			if (strpos($row2['action'], '_deleted') !== false)
			{
				echo "this sell delegation is just deleted by the user.\n";
				continue;
			}			
			
			$money = $dealed_price * $request_share;
			// add fee and tax
			$money = $money * (1 - SECURITY_TRANSACTION_TAX - SECURITY_TRANSACTION_FEE);
			echo "to sell , money: $money\n";
				
			$db->sql_transaction('begin');

			// Remove the stock share from the user
			$query2		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);	
								
			$db->sql_freeresult($result2);
			
			if ($row2)
			{
				$share 			= (int)$row2['share'];
				$share_to_sell 	= (int)$row2['share_to_sell'];
				echo "share:$share share_to_sell:$share_to_sell\n";
					
				if ($request_share == $share)
				{
					// remove the corp stock entry
					$sql = 'DELETE FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
					$db->sql_query($sql);		
					echo $sql . PHP_EOL;				
				}
				else if ($share > $request_share)
				{
					$sql 	= 'UPDATE sv_holding_stock SET share=share-' . $request_share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	     			$db->sql_query($sql);
	     			echo $sql . PHP_EOL;
     			}
     			else
     			{
	     			// This should not happen. There must be something wrong. 	
		     		echo '\tERROR: No enough shares' . PHP_EOL;
		     		$db->sql_transaction('rollback');
		     		continue;	     		
     			}
     			
     			// we handle share_to_sell seperately to update it correctly. share_to_sell is a varialbe related to UI onsell page.
     			// Consider this scenario: the user wants to sell corp A and send out a sell delegation. 
     			// The trading time service starts to iterate all transactions and this sell delegation is on the list.
     			// Then the user delete the sell delegation and the action/share_to_sell fields are updated by the UI page.
     			// As this delegation is already on the list of trading time service, it will still be executed.
     			// So we need to handle this case and we should update share and share_to_sell seperately.
     			if ($share_to_sell >= $request_share)
     			{
	      			$sql 	= 'UPDATE sv_holding_stock SET share_to_sell=share_to_sell-' . $request_share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       			$db->sql_query($sql);	
	       			echo $sql . PHP_EOL;					
				}
			}					
						
			// Give the money to the user
			$sql = 'UPDATE phpbb_users SET user_money=user_money+' . $money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
						
			// Update transaction status
			if ($action == 'to_sell')
				$sql = 'UPDATE sv_transaction SET action="sell", deal_time=' . (get_taiwan_time()) . ', price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
			else
				$sql = 'UPDATE sv_transaction SET action="sell_off_hour", deal_time=' . (get_taiwan_time()) . ', price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
				
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
				
			$db->sql_transaction('commit');
			echo "sell - done\n";
		}
		// to short sell
		else if ($action == 'to_short_sell')
		{
			// we require 90% amount as deposit.
			$needed_deposit = $dealed_price * $request_share * DEPOSIT_FOR_SHORT_SELL;
			// tax.
			$tax = $dealed_price * $request_share * SECURITY_TRANSACTION_TAX;
			echo "To short sell, needed deposit: $needed_deposit, tax: $tax \n";
						
			// Check if the user has enough money
			$query2	= "SELECT * FROM phpbb_users WHERE user_id=" . $user_id; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);
			$user_money = $row2['user_money'];
					
			$db->sql_freeresult($result2);
			
			echo "money: $user_money\n";
					
			if ($user_money < $needed_deposit + $tax)
			{
				echo "Not enough money\n";
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="money_not_enough"  WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				continue;
			}
			
			$db->sql_transaction('begin');
						
			// Add the short stock to the user
			$query2		= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);		
			
			$db->sql_freeresult($result2);
							
			if ($row2)
			{
				// the user already has the short stock 
				$share 		= (int)$row2['share'] + (int)$request_share;
				$deposit	= (int)$row2['deposit'] + (int)$needed_deposit;				
	       		
	       		// calculate the average cost
	       		$avg_cost 	= ($dealed_price * $request_share + $row2['share'] * $row2['cost']) / ($request_share + $row2['share']);
	       		
	       		$sql 		= 'UPDATE sv_short_stock SET share=' . $share . ', deposit=' . $deposit . ', cost=' . $avg_cost . '  WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       		$db->sql_query($sql);	
	       		echo $sql . PHP_EOL;
			}
			else
			{
				$sql = 'INSERT INTO sv_short_stock (user_id, corp_id, share, cost, deposit) VALUES ("' . $user_id . '", "'. $corp_id . '", "' . $request_share . '", "' . $dealed_price . '", "' . $needed_deposit . '")';
				echo $sql;
           		$db->sql_query($sql);
           		echo $sql . PHP_EOL;
       		}
						
			// Take the money from the user
			$needed_money = $needed_deposit + $tax;
			$sql = 'UPDATE phpbb_users SET user_money=user_money-' . $needed_money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
						
			// Update transaction status
			$sql = 'UPDATE sv_transaction SET action="short_sell", deal_time=' . (get_taiwan_time()) . ',price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;			
						
			$db->sql_transaction('commit');
			echo "short sell - done\n";
		}
		// to short cover
		else if ($action == 'to_short_cover')
		{
			// we need to double confirm if this delegation is still not deleted by the user.
			$sql2	= "SELECT * FROM sv_transaction WHERE transaction_id=$transaction_id"; 
			$result2 = $db->sql_query($sql2); 
			$row2 = $db->sql_fetchrow($result2);
			
			$db->sql_freeresult($result2);
			
			if ($row2['action'] != 'to_short_cover')
			{
				echo "this short_cover delegation is just deleted by the user.\n";
				continue;
			}			
			
			$query2	= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);						
			
			$db->sql_freeresult($result2);
			
			if ($row2 == false)
			{
				echo 'fail to find correct  record in sv_short_stock.\n';
				continue;
			}
			
			$cost 					= $row2['cost'];
			$deposit 				= (int)$row2['deposit'];	
			$share 					= (int)$row2['share'];
			$share_to_short_cover 	= (int)$row2['share_to_short_cover'];
			echo "share:$share share_to_short_cover:$share_to_short_cover, deposit:$deposit\n";					
						
			// calculate gain/loss
			$gain_or_loss = (int)(($cost - $dealed_price ) * $request_share);
			// fee
			$fee = (int)($dealed_price * $request_share * SECURITY_TRANSACTION_FEE);
			echo "to short cover , cost:$cost, dealed_price:$dealed_price, gain_or_loss: $gain_or_loss, fee: $fee\n";			
				
			// Calculate deposit which should be returned to the user.
			$return_deposit = $deposit * ((double)$request_share / (double)$share);
			echo "return deposit: $return_deposit\n";
	
			// Check if the user has enough money to buy the shares and pay fee		
			$query2	= "SELECT * FROM phpbb_users WHERE user_id=" . $user_id; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);
			$user_money = $row2['user_money'];
			
			$db->sql_freeresult($result2);
			
			if ($user_money + $return_deposit + $gain_or_loss < $fee)
			{
				echo "Not enough money\n";
				echo "user_money:$user_money, return_deposit:$return_deposit, gain_or_loss:$gain_or_loss, fee:$fee \n";
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ', check_status="money_not_enough"  WHERE transaction_id=' . $transaction_id;				
				$db->sql_query($sql2);
				echo $sql2 . PHP_EOL;
					
				continue;	
			}
						
			$db->sql_transaction('begin');
			
			// Remove the short stock share from the user				
			if ($request_share == $share)
			{
				// remove the corp stock entry
				$sql = 'DELETE FROM sv_short_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
				$db->sql_query($sql);						
				echo $sql . PHP_EOL;
			}
			else if ($share > $request_share)
			{
				$sql 	= 'UPDATE sv_short_stock SET share=' . ($share - $request_share) . ', deposit=' . ($deposit - $return_deposit) . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
				$db->sql_query($sql);
				echo $sql . PHP_EOL;				
     		}
     		else
     		{
	     		// This should not happen. There must be something wrong. 	
	     		echo '\tERROR: No enough shares' . PHP_EOL;
	     		$db->sql_transaction('rollback');
	     		continue;	     		
     		}
     			
     		// we handle share_to_sell seperately to update it correctly. share_to_sell is a varialbe related to UI onsell page.
     		// Consider this scenario: the user wants to sell corp A and send out a sell delegation. 
     		// The trading time service starts to iterate all transactions and this sell delegation is on the list.
     		// Then the user delete the sell delegation and the action/share_to_sell fields are updated by the UI page.
     		// As this delegation is already on the list of trading time service, it will still be executed.
     		// So we need to handle this case and we should update share and share_to_sell seperately.
     		if ($share_to_short_cover >= $request_share)
     		{
	      		$sql 	= 'UPDATE sv_short_stock SET share_to_short_cover=share_to_short_cover-' . $request_share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       		$db->sql_query($sql);						
	       		echo $sql . PHP_EOL;
			}
			
						
			// Take the money from the user
			$user_money = $user_money + $return_deposit + $gain_or_loss - $fee; 
			$sql = 'UPDATE phpbb_users SET user_money=' . $user_money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
							
			// Update transaction status
			$sql = 'UPDATE sv_transaction SET action="short_cover", deal_time=' . (get_taiwan_time()) . ', price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			echo $sql . PHP_EOL;
					
			$db->sql_transaction('commit');
				
			
			echo "short_cover - done\n";
		}
		

		// After the deal is done, check if the corp name is empty or not. If empty, we may loss this corp and send a notification mail. 
		if (get_corp_chinese_name($corp_id) == '')
		{
			$message = 'Corp ' . $corp_id . ' may be missed.';
			mail( "stockvirtual.info@gmail.com", "{Possible Missed Corp", $message, "From: " );
		}
	} // while ($thisrow = $db->sql_fetchrow($result))	
	
	$db->sql_freeresult($result);
}

// clear unfinished transactions after the end of a trading day.
function clear_transaction()
{
	global $db, $time;
	
//	$current_trading_day_start_time = get_current_trading_day_start_time(get_taiwan_time()); 	
	
	$sql	= "SELECT * FROM sv_transaction WHERE action='to_buy' OR action='to_sell' OR action='to_short_cover' OR action='to_short_sell' OR action='to_sell_off_hour' ORDER BY corp_id"; 
	$result = $db->sql_query($sql); 
		
	$saved_corp_id = '';
		
	while ($thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id 		= $thisrow['corp_id'];
    	$transaction_id	= $thisrow['transaction_id'];    	
    	echo "Checking transaction_id: $transaction_id  corp_id:$corp_id\n";

    	$user_id		= $thisrow['user_id'];
		$request_share	= $thisrow['share'];
		$request_price	= $thisrow['request_price'];
		$action			= $thisrow['action'];
		echo "user_id:$user_id request_share:$request_share request_price:$request_price action:$action \n";
		
    	// Delete the delegations checked in the previous trading day
    	$request_time	= $thisrow['request_time'];
    	$check_time		= $thisrow['check_time'];
    	if ($check_time != '')
    	{
	    	$check_date 			= get_date_from_time($check_time);
	    	$latest_trading_date 	= get_latest_trading_date($time);
	    	
	    	if ($check_date <= $latest_trading_date)
	    	{	    	
	    		echo "Delete the delegations checked in the previous trading day\n";	    	
	    		delete_delegation($transaction_id);
	    		continue;
    		}
    	}
    	
	}
	
	$db->sql_freeresult($result);
}



// update all the user values, including the cash money and holding stocks. 
// Also update the performance for 1, 3 and 6 month period.
function update_all_user_value()
{
	global $db, $time;	
	
	$sql	= 'SELECT * FROM phpbb_users WHERE user_type=' . USER_NORMAL;
	$result = $db->sql_query($sql);
	
	$stock_price = array();
	
	// period of ROI
	$period 					= array(1 => 1, 2 => 3, 3 => 6, 4 => 12, 5 => 24, 6 => 36, 7 => 48, 8 => 60);
	
	// infor for whole site ROI statistics
	$user_value		= array();
	
	$user_roi			= array(
						1  => array(),
						3  => array(),
						6  => array(),
						12 => array(),
						24 => array(),
						36 => array(),
						48 => array(),
						60 => array(),
						);	
	
	// running time info 
	$user_value_already_exists 	= 0;
	$get_stock_price 			= 0;	
	$update_value 				= 0;
	$cal_roi					= 0;
	$get_previous_value			= 0;
	$update_roi					= 0;
		
	
	// for each normal user
	while ($thisrow = $db->sql_fetchrow($result))
	{		
		$user_begin_time = microtime(true);
		
		$user_id 	= $thisrow['user_id'];
		$money 		= $thisrow['user_money'];
		$value		= $money;
		$date 		= get_latest_trading_date($time);

		echo "user_id:$user_id money:$money\n";
		
		// remember the max portion info.
		$max_corp_id	= 'Cash';			// use cash as default for simplicity.
		$max_value		= $money;
		
		$s = microtime(true);
		// Check if the user value already exists.
		$sql2 	= "SELECT * FROM sv_user_value WHERE user_id=$user_id ORDER BY date DESC";
		//echo	"$sql2\n";
		$result2 	= $db->sql_query_limit($sql2, 1);
		if (($row2 = $db->sql_fetchrow($result2)))
		{
			$previous_value = $row2['value'];
			$previous_date 	= $row2['date'];
			
			if ($date == $previous_date)
			{
				$user_value_already_exists += (microtime(true) - $s);
				echo "user:$user_id date:$date already exists.\n";
				continue;			
			}
		}	
		else
		{
			// no existing record for this user, set it as the default cash
			$previous_value	= 10000000;
		}
		$user_value_already_exists += (microtime(true) - $s);
		
		$sql2		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id;
		$result2 	= $db->sql_query($sql2);

		// check all stocks. 
		while ($thisrow2 = $db->sql_fetchrow($result2))
		{
			$corp_id 	= $thisrow2['corp_id'];
			$share		= $thisrow2['share'];
			echo "corp_id:$corp_id share:$share\n";
			
			// get the latest corp price.
			$price		= 0;			
			
			if (isset($stock_price[$corp_id]))
			{
				echo "$corp_id price already got\n";
				$price = $stock_price[$corp_id];
			}
			else
			{		
				$s = microtime(true);
					
				$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
				$result3 	= $db->sql_query_limit($sql3, 1); 
				if ($thisrow3 = $db->sql_fetchrow($result3))
				{
					$price = $thisrow3['price'];
					echo "price:$price \n";
					
					$stock_price[$corp_id] = $price;
				}
				else
				{
					// there is a holding stock but there is no price info. send out notification. 
					$message = 'Corp ' . $corp_id . ' may have problems. The price is not available while we are calculating the user value.';
					mail( "stockvirtual.info@gmail.com", "{Possible Missed Corp - calculating user market value}", $message, "From: " );
				}	
				
				$get_stock_price += (microtime(true) - $s);		
			}
			
			$value += $price * $share;	
			
			if ($price * $share > $max_value)
			{
				$max_corp_id	= $corp_id;
				$max_value		= $price * $share;
			}	
		}
		
		// check short stock and deposit
		$sql2		= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id;
		$result2 	= $db->sql_query($sql2);
		
		$total_deposit	= 0;
		$total_gain_or_loss = 0;
		
		while ($thisrow2 = $db->sql_fetchrow($result2))
		{
			$corp_id 	= $thisrow2['corp_id'];
			$share		= $thisrow2['share'];
			$cost		= $thisrow2['cost'];
			$deposit	= $thisrow2['deposit'];
			echo "corp_id:$corp_id, share:$share, deposit:$deposit, cost:$cost\n";
			
			// get the latest corp price.
			$price		= 0;
			
			if (isset($stock_price[$corp_id]))
			{
				echo "$corp_id price already got\n";
				$price = $stock_price[$corp_id];
			}
			else
			{	
				$s = microtime(true);
						
				$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
				$result3 	= $db->sql_query_limit($sql3, 1); 
				if ($thisrow3 = $db->sql_fetchrow($result3))
				{
					$price = $thisrow3['price'];
					echo "price:$price \n";
					
					$stock_price[$corp_id] = $price;
				}
				else
				{
					// there is a holding stock but there is no price info. send out notification. 
					$message = 'Corp ' . $corp_id . ' may have problems. The price is not available while we are calculating the user value.';
					mail( "stockvirtual.info@gmail.com", "{Possible Missed Corp - calculating user market value}", $message, "From: " );
				}
				
				$get_stock_price += (microtime(true) - $s);		
			}			
			
			
			$gain_or_loss = ($cost - $price) * $share;
			echo "gain_or_loss:$gain_or_loss\n";
			
			$total_gain_or_loss += $gain_or_loss;
			$total_deposit		+= $deposit;			
		}
		
		echo "total_deposit:$total_deposit, total_gain_or_loss:$total_gain_or_loss\n";
		$value = $value + $total_deposit + $total_gain_or_loss;		
		echo "total value: $value\n";
		
		// check if maintenance rate < 120%
		$maintenance_rate = cal_maintenance_rate($total_deposit, $total_gain_or_loss);
		if ($maintenance_rate < 120)
		{
			// force to short cover shorting stock because o too low maintenance rate 
			force_short_cover_due_to_maintenance($user_id);
		}
		
				
		// get the previous user value and check if the increase/decrease is abnormal.		
		$r = ($value - $previous_value)/$previous_value;
			
		if ($r > 0.1 || $r < -0.1)
		{
			$message = 'User value of ' .  get_username($user_id) . ' ( '  . $user_id . ' ) may have problems.' . "\r\n";
			$message .= 'The value change ' . round($r * 100, 1) . '% is over 10%.' . "\r\n";
			$message .= 'Previous value: ' . number_format($previous_value) . ' Now value: ' . number_format($value) . "\r\n";
			$message .= 'Click to check: http://stockvirtual.info/query_user.php?mode=query_user&user_id=' . $user_id . "\r\n";
			$message .= 'Max portion: ' . get_corp_chinese_name($max_corp_id) . '(' . $max_corp_id . ') ' . number_format($max_value) . "\r\n";
			echo $message;
			mail( "stockvirtual.info@gmail.com", "{Abnormal User Value}", $message, "From: " );
		}
			
		
		$s = microtime(true);
		// update the user value.
		$sql2 = "INSERT INTO sv_user_value (user_id, value, money, date) VALUES ($user_id, $value, $money, $date)";
		//echo "$sql2 \n";
		$db->sql_query($sql2);
		
		$update_value += (microtime(true) - $s);	
		
		$s = microtime(true);
		
		// Calculate the ROI performance over x month				
		for ($i = 1; $i <= sizeof($period); ++$i)
		{
			// Check if the user has a value record before that time.
			$time_x_month_before = $time - 30 * 24 * 60 * 60 * $period[$i];	
			$date_x_month_before = get_date_from_time($time_x_month_before);
		
			$s2 = microtime(true);
			$sql2	 = 'SELECT * FROM sv_user_value  WHERE user_id=' . $user_id . ' AND date < "' . $date_x_month_before . '" ORDER BY date DESC';
			$result2 = $db->sql_query_limit($sql2, 1); 					
			if ($thisrow2 = $db->sql_fetchrow($result2))
			{				
				
				$get_previous_value += (microtime(true) - $s2);	
				
				$old_value = $thisrow2['value'];
				$roi = (float)($value - $old_value)/(float)$old_value;
				//echo "user_id:$user_id ROI:$roi period:" . ($period[$i] * 30) . "\n";
				
				
				$s3 = microtime(true);
				// Insert this ROI performance record, or update if already exists.
				$sql2	 = 'SELECT * FROM sv_user_roi  WHERE user_id=' . $user_id . ' AND period=' . ($period[$i] * 30);
				$result2 = $db->sql_query_limit($sql2, 1); 
				if ($thisrow2 = $db->sql_fetchrow($result2))
				{					
					$sql2 	= 'UPDATE sv_user_roi SET roi=' . $roi . ' WHERE user_id=' . $user_id . ' AND period=' . ($period[$i] * 30);
					//echo "$sql2 \n";
					$result2 = $db->sql_query($sql2); 
				}
				else
				{
					$sql2 = "INSERT INTO sv_user_roi (user_id, roi, period) VALUES ($user_id, $roi, " . ($period[$i] * 30) . ")";
					//echo "$sql2 \n";
					$result2 = $db->sql_query($sql2); 
				}	
				
				// update total user ROI statistics
				$p = $period[$i];
				$user_roi["$p"]["$user_id"] = $roi;
				echo "period:$p, user:$user_id, roi:$roi\n";
				
				$update_roi += (microtime(true) - $s3);	
							
			}
			else
			{
				$get_previous_value += (microtime(true) - $s2);	
			}
		}
		
		$cal_roi += (microtime(true) - $s);	
		
		$user_time = microtime(true) - $user_begin_time;
		echo "this user takes $user_time seconds \n";		
		
		// update total user ROI statistics
		$user_value["$user_id"] = $value;
		//echo "user:$user_id, value:$value\n";
		
		// running time info
		echo "Accumulated running time info:\n";
		echo "user_value_already_exists:\t$user_value_already_exists \n";
		echo "get_stock_price:\t$get_stock_price \n";		
		echo "update_value:\t$update_value \n";
		echo "cal_roi:\t$cal_roi (get_previous_value:$get_previous_value + update_roi:$update_roi)\n";
		echo "\n";
		
	}	// for each normal user
	
	
	// whole site ROI statistics	
	$base_value 	= array();
	$current_value 	= array();
		
	for ($i = 1; $i <= sizeof($period); ++$i)
	{
		$p = $period[$i];
		$user_num_for_this_period = sizeof($user_roi["$p"]);
		
		// sort by ROI from high to low
		arsort($user_roi["$p"]);		
		
		// calculate user value sum for each band (from 10% to 100%).
		$j = 0;
		foreach ($user_roi["$p"] as $id => $roi)
		{    		
			for ($k = 1; $k <= 10; ++$k)
			{
				if ($j < $user_num_for_this_period *$k / 10)
				{
					$current_value[$k] 	+= $user_value["$id"];
					$base_value[$k] 	+= ($user_value["$id"] / (1 + $roi));
				}	
			}			
			
			++$j;
		}
		
		// calculate ROI for each band
		for ($k = 1; $k <= 10; ++$k)
		{
			if ($base_value[$k] != 0)
				$roi =	($current_value[$k] - $base_value[$k]) / $base_value[$k];			
			else
				$roi = 0;
			//echo "period:$p, band $k, roi:$roi\n";
			
			// update to database
			$sql = 'INSERT IGNORE INTO sv_whole_site_roi SET date="' . $date . '", period=' . ($p * 30) . ', band=' . ($k * 10) . ', roi=' . $roi;
			//echo "$sql \n";
    		$result	= $db->sql_query($sql); 		
		}			
	}			
	
	// calculate ROI for TAIEX (band 0% in database)
	// get the latest TAIEX
	$sql	 = 'SELECT * FROM sv_taiex ORDER BY date DESC';
	$result = $db->sql_query_limit($sql, 1);
	if ($thisrow = $db->sql_fetchrow($result))
	{
		$current_index = $thisrow['closing_index'];
	
		// get the previous TAIEX index to calculate ROI 
		for ($i = 1; $i <= sizeof($period); ++$i)
		{
			$p = $period[$i];
			
			$time_x_month_before = $time - 30 * 24 * 60 * 60 * $period[$i];	
			$date_x_month_before = get_date_from_time($time_x_month_before);
		
			$sql	 = 'SELECT * FROM sv_taiex  WHERE date < "' . $date_x_month_before . '" ORDER BY date DESC';
			$result = $db->sql_query_limit($sql, 1);
			if ($thisrow = $db->sql_fetchrow($result))
			{						
							
				$old_index = $thisrow['closing_index'];
				
				if ($old_index != 0)
					$roi = (float)($current_index - $old_index)/(float)$old_index;
				else
					$roi = 0;
				//echo "TAIEX ROI:$roi period:" . ($period[$i] * 30) . "\n";
				
				$sql2 = 'INSERT IGNORE INTO sv_whole_site_roi SET date="' . $date . '", period=' . ($p * 30) . ', band=0, roi=' . $roi;
				//echo "$sql2 \n";
				$result2 = $db->sql_query($sql2); 
			}	
		}
	}
	
	// running time info
	$t = $user_value_already_exists + $get_stock_price + $update_value + $cal_roi + 1; 
	$user_value_already_exists_percent 	= $user_value_already_exists * 100 / $t;
	$get_stock_price_percent 			= $get_stock_price * 100 / $t;
	$update_value_percent 				= $update_value * 100 / $t;
	$cal_roi_percent 					= $cal_roi * 100 / $t;
	$get_previous_value_percent			= $get_previous_value * 100 / $t;
	$update_roi_percent 				= $update_roi * 100 / $t;		
	
	echo "Total running time info:\n";
	echo "user_value_already_exists:\t$user_value_already_exists ($user_value_already_exists_percent%)\n";
	echo "get_stock_price:\t$get_stock_price ($get_stock_price_percent%)\n";	
	echo "update_value:\t$update_value ($update_value_percent%)\n";
	echo "cal_roi:\t$cal_roi ($cal_roi_percent%) \n";
	echo "get_previous_value:$get_previous_value ($get_previous_value_percent%)  update_roi:$update_roi ($update_roi_percent%)\n";
	echo "\n";
	
}

// check is the transaction is done or not
function is_done($action)
{
	if ($action == 'buy' || $action == 'sell' || $action == 'short_sell' || $action == 'short_cover' )
		return true;
		
	return false;
}


// delete/cancel the delegation
function delete_delegation($trans_id)
{
	global $db;	
	
	$sql		= 'SELECT * FROM sv_transaction WHERE transaction_id=' . $trans_id;
	$result 	= $db->sql_query_limit($sql, 1); 
	if ($thisrow = $db->sql_fetchrow($result))
	{
		$action = $thisrow['action'];
		
		// check if this delegation is already deleted.
		if (strpos($action, '_deleted') !== false)
			return false;
		
		// restore the to sell number of the holding stock.
		if ($action == 'to_sell' || $action == 'to_short_cover' || $action == 'to_sell_off_hour')
		{
			$user_id 	= $thisrow['user_id'];
			$corp_id 	= $thisrow['corp_id'];
			$share		= $thisrow['share'];			

			if ($action == 'to_sell' || $action == 'to_sell_off_hour')
			{
				$sql		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
				$result 	= $db->sql_query_limit($sql, 1);
				if ($thisrow = $db->sql_fetchrow($result))
				{
					if ((int)$thisrow['share_to_sell'] < $share)
					{
						// something wrong.
						echo "UNEXPECTED ERROR: share_to_sell < share \n";
						return false;
					}
				
					$share_to_sell = (int)$thisrow['share_to_sell'] - $share;
	
					$db->sql_transaction('begin');	
	
					$sql 	= 'UPDATE sv_holding_stock SET share_to_sell =' . $share_to_sell . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
					$db->sql_query($sql);
				}
			}
			// $action == 'to_short_cover'
			else
			{
				$sql		= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
				$result 	= $db->sql_query_limit($sql, 1);
				if ($thisrow = $db->sql_fetchrow($result))
				{
					if ((int)$thisrow['share_to_short_cover'] < $share)
					{
						// something wrong.
						echo "UNEXPECTED ERROR: share_to_short_cover < share \n";
						return false;
					}
				
					$share_to_short_cover = (int)$thisrow['share_to_short_cover'] - $share;
	
					$db->sql_transaction('begin');	
	
					$sql 	= 'UPDATE sv_short_stock SET share_to_short_cover =' . $share_to_short_cover . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
					$db->sql_query($sql);
				}			
			}		
			
			$action = $action . '_deleted';
			$sql 	= 'UPDATE sv_transaction SET action="' . $action . '" WHERE transaction_id=' . $trans_id;
			$db->sql_query($sql);	
			
			$db->sql_transaction('commit');
			
		}
		else
		{			
			$action = $action . '_deleted';
			$sql 	= 'UPDATE sv_transaction SET action="' . $action . '" WHERE transaction_id=' . $trans_id;
			$db->sql_query($sql);
		}
		
		return true;
	}
	
	return false;
}

// force to short cover shorting stock
//
// reason: 0 for equity change event, 1 for too low maintenance rate 
function force_short_cover($user_id, $corp_id, $reason = 0)
{
	global $db;
	
	$sql	= 'SELECT * FROM sv_short_stock WHERE user_id = ' . $user_id . ' AND corp_id="' . $corp_id . '"';
	$result = $db->sql_query_limit($sql, 1);	
	if ($row = $db->sql_fetchrow($result))
	{
		$shorting_stock_id		= $row['holding_stock_id'];			
		$user_id				= $row['user_id'];			
		$cost 					= $row['cost'];
		$deposit 				= (int)$row['deposit'];	
		$share 					= (int)$row['share'];
		$share_to_short_cover 	= (int)$row['share_to_short_cover'];
		echo "Force to cover: corp_id:$corp_id, user_id:$user_id, share:$share, cost:$cost, deposit:$deposit, share_to_short_cover:$share_to_short_cover \n";					

		// get the refered price 
		$refered_price = get_refered_price($corp_id);
		
		// calculate gain/loss
		$gain_or_loss = (int)(($cost - $refered_price ) * $share);
		// fee
		$fee = (int)($refered_price * $share * SECURITY_TRANSACTION_FEE);
		echo "to short cover , cost:$cost, refered_price:$refered_price, gain_or_loss: $gain_or_loss, fee: $fee\n";			

		// Check if the user has enough money to buy the shares and pay fee		
		$query		= "SELECT * FROM phpbb_users WHERE user_id=" . $user_id; 
		$result	= $db->sql_query_limit($query, 1);
		$row		= $db->sql_fetchrow($result);
		$user_money = $row['user_money'];			
			
		if ($user_money + $deposit + $gain_or_loss < $fee)
		{
			// this should not happen because we should already force to cover when the deposit cannot pay the loss.				
			echo "Not enough money\n";
			echo "user_money:$user_money, deposit:$deposit, gain_or_loss:$gain_or_loss, fee:$fee \n";				
					
			continue;	
		}
						
		$db->sql_transaction('begin');
			
		// Remove the short stock share from the user				
		$sql = 'DELETE FROM sv_short_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
		echo "Remove the short stock share from the user: $sql \n";
		$db->sql_query($sql);

		// Take the money from the user
		$user_money = $user_money + $deposit + $gain_or_loss - $fee; 
		$sql = 'UPDATE phpbb_users SET user_money=' . $user_money . ' WHERE user_id=' . $user_id;
		echo "Take the money from the user: $sql \n";
		$db->sql_query($sql);
							
		// Delete pending transactions 
		if ($share_to_short_cover != 0)
		{
			echo "Delete pending transactions \n";
				
			$sql4	= 'SELECT * FROM sv_transaction WHERE corp_id="' . $corp_id . '" AND user_id="' . $user_id . '" AND action="to_short_cover"';
			$result4 = $db->sql_query($sql4);	
			while ($row4 = $db->sql_fetchrow($result4))
			{
				$transaction_id	= $row4['transaction_id'];				
				
				$sql5 = 'UPDATE sv_transaction SET action="to_short_cover_deleted" WHERE transaction_id=' . $transaction_id;
				echo "Delete pending transaction: $sql5 \n";
				$db->sql_query($sql5);
			}
		}
			
		// Add a transaction for record
		if ($reason == 0)
			$action = 'forced_short_cover_equity';
		else if ($reason == 1)
			$action = 'forced_short_cover_maintenance';
		else
			$action = 'forced_short_cover';
		
		
		$sql = 'INSERT INTO sv_transaction ' . $db->sql_build_array('INSERT', array(
							'user_id'		=> $user_id,
							'corp_id'		=> $corp_id,
							'action'		=> $action,
							'share'			=> $share,
							'price'			=> $refered_price,
							'request_time'	=> get_taiwan_time(),
							'note'			=> '',
							'deal_time'		=> get_taiwan_time(),
							'check_time'	=> get_taiwan_time(),
							'check_status'	=> '',							
							)
						);
		echo "Add a transaction for record: $sql \n";
		$db->sql_query($sql);
					
		$db->sql_transaction('commit');
				
			
		echo "Force to short cover - done\n";
	}
}

// force to short cover shorting stock because o too low maintenance rate 
function force_short_cover_due_to_maintenance($user_id)
{
	global $db;
	
	$sql2		= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id;
	$result2 	= $db->sql_query($sql2);
		
	while ($thisrow2 = $db->sql_fetchrow($result2))
	{
		$corp_id 	= $thisrow2['corp_id'];
		force_short_cover($user_id, $corp_id, 1);
	}
}

// Handle all equity change events, including dispatch stock/cash dividend and decreasing equity.
function handle_equity_change_event()
{
	global $db, $time;
	
	echo "handle_equity_change_event - begin \n\n";
	
	$date = get_date_from_time($time);
	
	$sql	= 'SELECT * FROM sv_equity_change_event WHERE status="new"';
	$result = $db->sql_query($sql);	
	while ($row = $db->sql_fetchrow($result))
	{
		$scheduled_date			= $row['scheduled_date'];
		// If today is not the scheduled day, skip it.
		if ($date != $scheduled_date)
		{
			continue;
		}
		
		$equity_change_event_id = $row['equity_change_event_id'];
		$corp_id				= $row['corp_id'];
		$share_unit				= $row['share_unit'];
		$cash_after_change		= $row['cash_after_change'];
		$corp_id_after_change	= $row['corp_id_after_change'];
		$share_after_change		= $row['share_after_change'];
		$corp_id_after_change_2	= $row['corp_id_after_change_2'];
		$share_after_change_2	= $row['share_after_change_2'];
		$corp_id_after_change_3	= $row['corp_id_after_change_3'];
		$share_after_change_3	= $row['share_after_change_3'];
	
		echo "handle_equity_change_event, id: $equity_change_event_id, corp_id:$corp_id cash:$cash_after_change share:$share_after_change\n";
		if (is_valid_corp_id($corp_id) == false)
		{
			continue;
		}
		
		// get the refered price 
		$refered_price = get_refered_price($corp_id);
		
		// force to short cover all shorting stocks 
		$sql2	= 'SELECT * FROM sv_short_stock WHERE corp_id="' . $corp_id . '"';
		$result2 = $db->sql_query($sql2);	
		while ($row2 = $db->sql_fetchrow($result2))
		{			
			$user_id				= $row2['user_id'];			
			
			force_short_cover($user_id, $corp_id);			
		}		
		
		// handle holding stocks for all users
		$sql2	= 'SELECT * FROM sv_holding_stock WHERE corp_id="' . $corp_id . '"';
		$result2 = $db->sql_query($sql2);	
		while ($row2 = $db->sql_fetchrow($result2))
		{
			$holding_stock_id	= $row2['holding_stock_id'];			
			$user_id			= $row2['user_id'];
			$share				= $row2['share'];
			$cost				= $row2['cost'];
			
			echo "user:$user_id share:$share\n";
			
			// Calculate the share/cash which the user should get.
			$user_get_cash_after_change			= (int)($share * $cash_after_change / $share_unit);
			
			$user_get_corp_id_after_change		= $corp_id_after_change;
			$user_get_corp_id_after_change_2	= $corp_id_after_change_2;
			$user_get_corp_id_after_change_3	= $corp_id_after_change_3;
			
			$user_get_share_after_change		= (int)($share * $share_after_change / $share_unit);
			$user_get_share_after_change_2		= (int)($share * $share_after_change_2 / $share_unit);
			$user_get_share_after_change_3		= (int)($share * $share_after_change_3 / $share_unit);
			
			// Update the cost info, based on the assumption that no other corp shares are dispatched. 			
			$new_cost = $share * $cost / $user_get_share_after_change;
			
			$db->sql_transaction('begin');
			
			// Update user holding stock and cash.
			if ($corp_id == $user_get_corp_id_after_change)
			{
				// We will have same corp in the most cases. 
				$sql3 = 'UPDATE sv_holding_stock SET corp_id="' . $user_get_corp_id_after_change . '" WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET share=' . $user_get_share_after_change . ' WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET share_to_sell=0 WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET cost=' . $new_cost . ' WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			else
			{
				// Seldomly, the 1st corp id may change. 
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change . '", "' . $user_get_share_after_change . '", '. $new_cost . ' )';
				echo "$sql3 \n";
				$db->sql_query($sql3);

				$sql3 = 'DELETE FROM sv_holding_stock WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			
			if ($user_get_cash_after_change > 0)
			{
				$sql3 = 'UPDATE phpbb_users SET user_money=user_money+' . $user_get_cash_after_change . ' WHERE user_id=' . $user_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			if ($user_get_share_after_change_2 > 0)
			{
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change_2 . '", "' . $user_get_share_after_change_2 . '", "0")';
				echo "$sql3 \n";
				$db->sql_query($sql3);
				
			}
			if ($user_get_share_after_change_3 > 0)
			{
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change_3 . '", "' . $user_get_share_after_change_3 . '", "0")';
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			
			// Add equity change log.
			$sql3 = 'INSERT INTO sv_equity_change_log (equity_change_event_id, execution_time, user_id, corp_id, share, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3) VALUES ';
			$sql3 .= ' (' . $equity_change_event_id . ', ' . $time . ', ' . $user_id . ', "' . $corp_id . '", ' . $share . ', ' . $user_get_cash_after_change . ', ';
			$sql3 .= ' "'. $user_get_corp_id_after_change . '",' . $user_get_share_after_change . ', "' .  $user_get_corp_id_after_change_2 . '",' . $user_get_share_after_change_2 . ', "' .  $user_get_corp_id_after_change_3 . '",' . $user_get_share_after_change_3 . ')';
			echo "$sql3 \n";
			$db->sql_query($sql3);
			
			$db->sql_transaction('commit');
			
		}
		
		// Update the event status and execution date. 
		$sql2 = 'UPDATE sv_equity_change_event SET status="executed" WHERE equity_change_event_id=' . $equity_change_event_id;
		echo "$sql2 \n";
		$db->sql_query($sql2);
		$sql2 = 'UPDATE sv_equity_change_event SET execution_date="' . get_date_from_time($time) . '" WHERE equity_change_event_id=' . $equity_change_event_id;
		echo "$sql2 \n";
		$db->sql_query($sql2);
	}
	
	echo "handle_equity_change_event - end \n\n";
}

// Get the TWSE value (price*volume) from Market Information System.
function get_TWSE_value()
{
    $raw_file = file_get_contents("http://mis.twse.com.tw/data/TSEIndex.csv");
    if ($raw_file == false)
    	return false;

    $csv 	= sv_str_getcsv ($raw_file);
    $value 	= ereg_replace("[^0-9]", "", $csv[0][164]);
	
	return $value;
}

// Get the OTC value (price*volume) from Market Information System.
function get_OTC_value()
{
    $raw_file = file_get_contents("http://mis.twse.com.tw/data/OTCIndex.csv");
    if ($raw_file == false)
    	return false;

    $csv 	= sv_str_getcsv ($raw_file);
    $value 	= ereg_replace("[^0-9]", "", $csv[0][98]);    
	
	return $value;
}

// Get the table prefix of knowledge tables.
function get_prefix_of_knowledge_table()
{
	return 'drupal7_';	
}

// Check if the knowledge databses exist or not.
function is_knowledge_db_ok()
{
	// disable knowledge function from Oct 2014. 
	return false;
	
//	global $db;
	
//	$table_name = get_prefix_of_knowledge_table() . 'node';
	
//	$sql 	= 'SHOW TABLES LIKE "' . $table_name . '"';
//	$result = $db->sql_query($sql); 
//	if ($row = $db->sql_fetchrow($result))
//	{
//		return true;
//	}
	
//	return false;
}

// Get the titles of knowledge articles.
function get_knowledge_titles()
{
	global $db;
	
	$table_name = get_prefix_of_knowledge_table() . 'node';
	$titles		= array();
	
	$sql 	= 'SELECT * FROM ' . $table_name;
	$result = $db->sql_query($sql); 
	while (($row = $db->sql_fetchrow($result)))
	{
		$id 	= $row['vid'];
		$title	= $row['title'];
		
		$titles[$id]	= $title;		
	}
	
	return $titles;
}

// Detect a URL in a String and Convert it to a Clickable Link
function autolink($string)
{
$content_array = explode(" ", $string);
$output = '';

foreach($content_array as $content)
{
//starts with http://
if(substr($content, 0, 7) == "http://")
$content = '<a href="' . $content . '" target="_blank">' . $content . '</a>';

//starts with www.
if(substr($content, 0, 4) == "www.")
$content = '<a href="http://' . $content . '" target="_blank">' . $content . '</a>';

$output .= " " . $content;
}

$output = trim($output);
return $output;
}

// Check if the user is following anyone.
function is_following_anyone($user_id)
{
	global $db;
	
	if ($user_id == '')
		return false;
		
	$sql = 'SELECT * FROM sv_follow_list WHERE follower_user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		return true;
	}
	else
	{
		return false;
	}
}

// Calculate and record the number of visiting daily, weekly and monthly users 
function cal_visiting_user_num()
{
	global $db;
	
	$now			= get_taiwan_time();
	$day_ago		= $now - 1 * 24 * 60 * 60;
	$week_ago		= $now - 7 * 24 * 60 * 60;
	$month_ago		= $now - 30 * 24 * 60 * 60;
	$quarter_ago	= $now - 90 * 24 * 60 * 60;
	$half_year_ago	= $now - 180 * 24 * 60 * 60;
	$year_ago		= $now - 365 * 24 * 60 * 60;
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $day_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$daily_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $week_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$weekly_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $month_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$monthly_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $quarter_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$quarterly_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $half_year_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$half_yearly_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $year_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$yearly_user_num = $row['num'];	
	$db->sql_freeresult($result);
	
	$date	= get_date_from_time($now);
	
	// insert the record into the database.
	$sql 	= 'SELECT * FROM sv_visiting_user WHERE date="' . $date . '"';
	$result = $db->sql_query($sql); 
	if ($db->sql_fetchrow($result) == false)	
	{
    	$sql2 = 'INSERT IGNORE INTO sv_visiting_user SET date="' . $date . '", daily_user_num=' . $daily_user_num . ', weekly_user_num=' . $weekly_user_num . ', monthly_user_num=' . $monthly_user_num . ', quarterly_user_num=' . $quarterly_user_num . ', half_yearly_user_num=' . $half_yearly_user_num . ', yearly_user_num=' . $yearly_user_num ;
    	$result2	= $db->sql_query($sql2); 
    	$db->sql_freeresult($result2);
	}
	$db->sql_freeresult($result);
	
	// generate lists of active users.  
	$daily_active_users 	= array();
	$weekly_active_users 	= array();
	$monly_active_users 	= array();
	
	$sql	= 'SELECT * FROM phpbb_users WHERE user_type=' . USER_NORMAL . ' ';
	$result = $db->sql_query($sql);	
	while ($thisrow = $db->sql_fetchrow($result))
	{		
		$user_id 			= $thisrow['user_id'];
		$user_lastvisit 	= $thisrow['user_lastvisit'];
	
		if ($user_lastvisit > $day_ago)
			$daily_active_users[] = $user_id;
		if ($user_lastvisit > $week_ago)
			$weekly_active_users[] = $user_id;
		if ($user_lastvisit > $month_ago)
			$monly_active_users[] = $user_id;
	}
	$db->sql_freeresult($result);
	
	$daily_list 	= implode(",", $daily_active_users);
	$weekly_list 	= implode(",", $weekly_active_users);
	$monthly_list 	= implode(",", $monly_active_users);
	
	// insert to database
	update_cache_info('daily_active_users', 	$daily_list);
	update_cache_info('weekly_active_users', 	$weekly_list);
	update_cache_info('monthly_active_users', 	$monthly_list);
	
}

function update_cache_info($description, $info)
{
	global $db;	
	
	$info_max_len = 4092;		// the field length in database
	
	if (strlen($info) >= $info_max_len)
		$info = substr($info, 0, $info_max_len);
	
	$sql 	= 'SELECT * FROM sv_cache_info WHERE description="' . $description . '"';
	$result = $db->sql_query($sql); 
	if (($row = $db->sql_fetchrow($result)) == false)	
	{
    	$sql2 		= 'INSERT INTO sv_cache_info SET info="' . $info . '", description="' . $description . '"';
    	$result2	= $db->sql_query($sql2); 
    	$db->sql_freeresult($result2);
	}
	else
	{
		$id = $row['cache_info_id'];
		
		$sql2 		= 'UPDATE sv_cache_info SET info="' . $info . '" WHERE cache_info_id=' . $id;
    	$result2	= $db->sql_query($sql2); 
    	$db->sql_freeresult($result2);		
	}
	$db->sql_freeresult($result);
}

function get_cache_info($description)
{
	global $db;	
	
	$info_max_len 	= 4092;		// the field length in database
	$info			= '';

	$sql 	= 'SELECT * FROM sv_cache_info WHERE description="' . $description . '"';
	$result = $db->sql_query($sql); 
	if (($row = $db->sql_fetchrow($result)) != false)	
	{
    	$info = $row['info'];
	}
	
	$db->sql_freeresult($result);
	
	return $info;
}

function update_short_sale_suspension_schedule()
{
	global $db;
	
	$url = "http://www.twse.com.tw/ch/trading/exchange/BFI84U/BFI84U.php#";
	$raw_file = file_get_contents($url);
	if ($raw_file == false)
	{
		echo "fail to get short_sale_suspension_schedule from TWSE.\n";
	   	return false;
	}

    $content	= iconv("BIG-5", "UTF-8//IGNORE", $raw_file);	// convert the web page from BIG-5 to UTF-8.
    $content 	= ereg_replace("&nbsp;", " &nbsp;", $content); // add one space to &nbsp; to make sure that we can check the empty tokens/fields correctly.
    $content	= strip_tags($content);    
    $begin		= strpos($content, '得為融資融券有價證券停資停券預告表'); 
    
    //echo "begin:$begin \n\n";
    if ($begin == 0)
    {
	    echo "fail to find the beginning string for short_sale_suspension_schedule from TWSE.\n";
    	return false;
	}
    	
    $begin	= $begin + strlen('得為融資融券有價證券停資停券預告表');
    $content = substr($content, $begin);     	
    
    $str = trim($content);
	$tok = strtok($str, " ");
	$i = 0;
	while ($tok !== false) 
	{
    	$corp_id = ereg_replace("[^0-9]", "", $tok); 
    	if (is_valid_corp_id($corp_id) && get_corp_chinese_name($corp_id) != '')
    	{
	    	// The first column should be corp id.
    		//echo "corp_id:$corp_id\n";    		
    	
    		// get the corp name
    		$name = strtok(" ");
    		//echo "name:$name\n";    		
    			
    		// get the following 4 dates.
    		$short_sale_suspension_date = strtok(" ");
    		$final_recall_date = strtok(" ");
    		$margin_purchasesuspension_date = strtok(" ");
    		$reinstatement_date = strtok(" ");
    		
    		// Make correct date format
    		$short_sale_suspension_date 	= ereg_replace("[^0-9]", "", $short_sale_suspension_date); 
    		$short_sale_suspension_date 	= convert_ROC_date($short_sale_suspension_date);
    		$final_recall_date 				= ereg_replace("[^0-9]", "", $final_recall_date); 
    		$final_recall_date 				= convert_ROC_date($final_recall_date);
    		$margin_purchasesuspension_date = ereg_replace("[^0-9]", "", $margin_purchasesuspension_date); 
    		$margin_purchasesuspension_date = convert_ROC_date($margin_purchasesuspension_date);
    		$reinstatement_date 			= ereg_replace("[^0-9]", "", $reinstatement_date); 
    		$reinstatement_date 			= convert_ROC_date($reinstatement_date);
    		
    		//echo "short_sale_suspension_date:$short_sale_suspension_date, final_recall_date:$final_recall_date, margin_purchasesuspension_date:$margin_purchasesuspension_date, reinstatement_date:$reinstatement_date \n";
    		
    		// get the corp name
    		$reason = strtok(" ");    		
    		//echo "reason:$reason\n";    		

    		
    		// Add to the DB: Suspension of Short Sale Forecast
    		$sql2	 = 'SELECT * FROM sv_short_sale_suspension  WHERE corp_id=' . $corp_id;
			$result2 = $db->sql_query_limit($sql2, 1); 
			if ($thisrow2 = $db->sql_fetchrow($result2))
			{					
				$sql2 	= 'UPDATE sv_short_sale_suspension 
							SET short_sale_suspension_date="' . $short_sale_suspension_date . '", 
								final_recall_date="' . $final_recall_date . '",
								margin_purchasesuspension_date="'. $margin_purchasesuspension_date . '",
								reinstatement_date="' . $reinstatement_date . '",
								reason="' . $reason . '" ' . ' 
							WHERE corp_id=' . $corp_id;
				//echo "$sql2 \n";
				$result2 = $db->sql_query($sql2); 
			}
			else
			{
				$sql2 = 'INSERT INTO sv_short_sale_suspension (corp_id, short_sale_suspension_date, final_recall_date, margin_purchasesuspension_date, reinstatement_date, reason) 
							VALUES ("' . $corp_id . '", "' . $short_sale_suspension_date . '", "' . $final_recall_date . '", "' . $margin_purchasesuspension_date . '", "' . $reinstatement_date . '", "' . $reason . '")';
				//echo "$sql2 \n";
				$result2 = $db->sql_query($sql2); 
			}				
		}
    	
    	$tok = strtok(" ");
    	++$i;    	
	}
}
	
	
// Convert ROC date to A.D. date. EX: 1010401 => 20120401
function convert_ROC_date($date)
{
	if (strlen($date) != 7)
		return '';

	// update the date to 20xx
    $y = (int)(substr($date, 0, 3)) + 1911;
    $d = $y . substr($date, 3, 2) . substr($date, 5, 2);	
    
    return $d;
}

// from http://phpcentral.com/208-url-validation-in-php.html
function isValidURL($url)
{
return preg_match('|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)?(/.*)?$|i', $url);
}

// get news related to hot companies.
function get_news()
{
	global $db;
	
	echo "get_news - begin\n\n";
	
	// get hot companies. 	
	$time_1_month_ago = get_taiwan_time() - 30 * 24 * 60 * 60;
	$sql 	= "SELECT corp_id, SUM(share*price) FROM sv_transaction where request_time > $time_1_month_ago GROUP BY corp_id  order by sum(share*price) desc";
	$result = $db->sql_query_limit($sql, 90); 
	$i = 0;
	while ($row = $db->sql_fetchrow($result))
	{
		$q 		= $row['corp_id'] . ' ' . get_corp_chinese_name($row['corp_id']);
		
		$r = search_for_news($row['corp_id'], $q);
		if ($r == false)
		{
			echo "Fail to search news, break and stop.\n\n";
			break;
		}
	}
	
	// get news about the whole stock market	
	$q 		= '台股 大盤';
	search_for_news(TAIEX_FOR_NEWS, $q);	
	
	echo "get_news - end\n\n";
}

function search_for_news($corp_id, $q)
{
	global $db;

	$search_key	= array(
		'AIzaSyBBJXeUHkMkb8T28QE7RuWIsAtcDKK8TdQ&cx=005254536160583838612:-jrjh3jrviw',		// stockvirtual.info
	
	);
			
	$key_index	= 0;
	
	do {
		$url 	= 'https://www.googleapis.com/customsearch/v1?key=' . $search_key[$key_index] . '&dateRestrict=d1&q=';	// search within 1 days	
		$url 	.= urlencode($q);	
		
		$raw_file = file_get_contents($url);
		if ($raw_file == false)
		{
			$key_index += 1;
			
			if ($key_index > sizeof())
			{
				echo "fail to get news for $q, url:$url\n";
				return false;
			}
			
			echo "Using search key number $key_index\n";
		}
		else
			break;
			
	}while(true);
	
	echo "\n" . $corp_id . ' ' . get_corp_chinese_name($corp_id) .  "\n";
	$data = json_decode($raw_file, true);	
//		print_r($data['items']);
		
	if (sizeof($data['items']) <= 0)
		return false;
		
	foreach ($data['items'] as $item)
	{
		echo "\n==========\n";
		echo $item['title'];
		echo "\n";
		echo $item['link'];
		echo "\n";
		//echo $item['snippet'];
		//echo "\n\n";
			
		if (isValidURL($item['link']) == false)
			continue;
						
		// extract the first important part of the news title
		$title 	= htmlspecialchars ($item['title']);		
		//echo "$title" . PHP_EOL;
		
		$t 		= explode("｜", $title);		
		$t 		= explode("_", $t[0]);
		
		print_r($t);
		$title 	= $t[0];
		//echo "$title" . PHP_EOL;

		$signature	= md5($title);
					
		// make sure no redundant news 
		$sql2	= 'SELECT * FROM sv_news where signature="' . $signature . '"';
		$result2= $db->sql_query_limit($sql2, 1);
		if ($db->sql_fetchrow($result2))
		{
			echo "news already exists\n";
		}
		else
		{
			// remove all quotes.
			$snippet	= htmlspecialchars ($item['snippet']);
			
						
			// insert the news				
			$sql2 = 'INSERT INTO sv_news (corp_id, title, snippet, link, signature, time) VALUES (
					"' . $corp_id . '" , "' .  $title . '" ,
					"' . $snippet . '" , "' .  $item['link'] . '" ,
					"' . $signature . '" , 
					' . get_taiwan_time() . ')';
 			echo $sql2;
    		$db->sql_query($sql2);			
		}
	}
	
	return true;
}


// get the corp id which the user with id $user_id may be interested, and $max is the max number of corp id returned.
function get_user_interested_corp($user_id, $max)
{
	global $db;
	
	if ($max <= 0 || $user_id <= ANONYMOUS)
		return;	

	$corp	= array();
	$r		= array();

	// load from DB
	$sql 	= "SELECT * FROM sv_user_interested_corp WHERE user_id=$user_id";
	$result = $db->sql_query($sql); 
	if ($row = $db->sql_fetchrow($result))
	{
		$corp_id_list = $row['corp_id_list'];
		
		if($corp_id_list == '')
			return $r;
			
		$corp = explode(',', $corp_id_list);		
	}
	else
	{
		// no record found. generate now.
		$corp = generate_user_interested_corp($user_id);
		
	}
	
	$i = 0;
	foreach($corp as $c)
	{
		$r[] = $c;	
		++$i;
			
		if ($i >= $max)
			break;
	}
	
	return $r;
}
	
// generate the corps which the user with id $user_id may be interested.
function generate_user_interested_corp($user_id)
{
	global $db;
	
	echo "<!--  generate_user_interested_corp: $user_id\n-->";
	
	$corp	= 	array();
	$corp[]	=	TAIEX_FOR_NEWS;		// assume everyone interested in TAIEX. 
	
	// check the user holding stock	
	$sql 	= "SELECT corp_id FROM sv_holding_stock WHERE user_id=$user_id GROUP BY corp_id";
	$result = $db->sql_query($sql); 
	while ($row = $db->sql_fetchrow($result))
	{
		$id = $row['corp_id'];
		if (is_valid_corp_id($id))
			$corp[] = $id;			
	}
	
	// check the user holding stock	
	$sql 	= "SELECT corp_id FROM sv_short_stock WHERE user_id=$user_id GROUP BY corp_id";
	$result = $db->sql_query($sql); 
	while ($row = $db->sql_fetchrow($result))
	{
		$id = $row['corp_id'];
		if (is_valid_corp_id($id))
			$corp[] = $id;			
	}
	
	
	// check the user's transaction history.	
	$time_3_month_ago = get_taiwan_time() - 90 * 24 * 60 * 60;
	$sql 	= "SELECT corp_id, SUM(share*price) FROM sv_transaction where request_time > $time_3_month_ago AND user_id=$user_id GROUP BY corp_id  order by sum(share*price) desc";
	$result = $db->sql_query_limit($sql, 20); 
	$i = 0;
	while ($row = $db->sql_fetchrow($result))
	{
		$id = $row['corp_id'];
		if (is_valid_corp_id($id))
			$corp[] = $id;		
	}
	
	// remove redundant corp id.	
	$corp = array_unique($corp);
	
	$corp_id_list = implode(",", $corp);
	
	echo "<!-- $corp_id_list  \n\n-->";	
	
	// store to DB
	$sql = 'INSERT INTO sv_user_interested_corp (user_id, corp_id_list) VALUES (' . $user_id . ', "' . $corp_id_list . '") ON DUPLICATE KEY UPDATE corp_id_list = "' . $corp_id_list . '"';
	//echo "  $sql\n\n";	
	$result = $db->sql_query($sql); 
	
	echo "<!--  generate_user_interested_corp - done  - $user_id\n -->";
	
	return $corp;
}

function generate_all_user_interested_corp()
{
	global $db;
		
	echo "generate_all_user_interested_corp - begin\n\n";
	
	$quarter_ago	= get_taiwan_time() - 90 * 24 * 60 * 60;
	
	$sql	= 'SELECT * FROM phpbb_users WHERE user_type=' . USER_NORMAL . ' AND user_lastvisit > ' . $quarter_ago;;
	$result = $db->sql_query($sql);	
	while ($thisrow = $db->sql_fetchrow($result))
	{		
		$user_id 	= $thisrow['user_id'];
	
		generate_user_interested_corp($user_id);
	}
	
	echo "generate_all_user_interested_corp - end\n\n";
}


function update_all_user_brief_history()
{
	global $db;
		
	echo "update_all_user_brief_history - begin\n\n";
	
	$quarter_ago	= get_taiwan_time() - 90 * 24 * 60 * 60;
	
	$sql	= 'SELECT * FROM phpbb_users WHERE user_type=' . USER_NORMAL . ' AND user_lastvisit > ' . $quarter_ago;;
	$result = $db->sql_query($sql);	
	while ($thisrow = $db->sql_fetchrow($result))
	{		
		$user_id 	= $thisrow['user_id'];
	
		generate_brief_history($user_id);
	}
	
	echo "update_all_user_brief_history - end\n\n";
}

// generate brief history of the user investment 
// period is the days within that period all transactions of one corp are considered as single transaction. 
function generate_brief_history($user_id, $significant_percent = 0.03, $period = 30)
{
	global $db;
	
	if ($user_id == ANONYMOUS)
		return;
	
	echo "generate_brief_history: $user_id, $significant_percent, $period <BR>\n";
	
	// clear the user's records.
	$sql2		= 'DELETE FROM sv_brief_history WHERE user_id=' . $user_id ;
	$result2 	= $db->sql_query($sql2);
	
	echo "generate_brief_history: checking holding stocks <BR>\n";
	
	$holding_share 	= array();
	$holding_cost	= array();
	$holding_price	= array();
	$holding_gain	= array();
	
	// get the latest user value.
	$sql2		= 'SELECT * FROM sv_user_value WHERE user_id=' . $user_id . ' ORDER BY date DESC';
	$result2 	= $db->sql_query_limit($sql2, 1);
	if (!($thisrow2 = $db->sql_fetchrow($result2)))
		return;
	
	$user_value = $thisrow2['value'];
	$threshold	= $user_value * $significant_percent;
	echo "value:$user_value, significant_percent:$significant_percent, threshold: $threshold <BR>\n";
	
	// Check the current holding stocks.
	$sql2		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id;
	$result2 	= $db->sql_query($sql2);
	while ($thisrow2 = $db->sql_fetchrow($result2))
	{
		$corp_id		 	= strval($thisrow2['corp_id']);
			
		// get the latest corp price.		
		$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
		$result3 	= $db->sql_query_limit($sql3, 1); 
		if (!($thisrow3 = $db->sql_fetchrow($result3)))
			continue;			
			
		$holding_price[$corp_id] 	= $thisrow3['price'];
		$holding_share[$corp_id]	= $thisrow2['share'];
		$holding_cost[$corp_id]		= $thisrow2['cost'];
		
		echo "corp_id:$corp_id share:$holding_share[$corp_id]  cost:$holding_cost[$corp_id]\n";		
		
		$holding_gain[$corp_id] = ($thisrow3['price'] - $thisrow2['cost']) * $thisrow2['share'];
	}
	
//	print_r($holding_gain);	
//	arsort ($holding_gain);	
//	print_r($holding_gain);	
	
	foreach ($holding_gain as $id => $g) 
	{
    	//echo "gain/loss: $id = $g<BR>";    	
    	if (abs($g) <= $threshold)
    		continue;
    	    		
    	$s	= $holding_share[$id];
    	$c	= $holding_cost[$id];
    	$p	= $holding_price[$id];
    		
    	echo "significant gain/loss, $s shares, cost:$c, price:$p<BR>\n";
    		
    	// check if the record already exists. deal_time is 0 for holding stocks.
    	$sql2		= 'SELECT * FROM sv_brief_history WHERE user_id=' . $user_id . ' AND corp_id="'. $id . '" AND deal_time=0';
		$result2 	= $db->sql_query($sql2);
		if ($thisrow2 = $db->sql_fetchrow($result2))
		{
			// update the record.
			$sql 		= 'UPDATE sv_brief_history SET share=' . $s . '
							, price=' . $p . ', cost=' . $c . '
							,gain_or_loss=' . $g . ' 
							 WHERE user_id=' . $user_id . ' AND corp_id="' . $id . '"';
							 
			echo $sql . '<BR>' . PHP_EOL;
	       	$db->sql_query($sql);			
		}
		else
		{    		
    		// add new record.
	    	$sql = 'INSERT INTO sv_brief_history (user_id, corp_id, share, price, cost, gain_or_loss, deal_time, status) VALUES (
    				' . $user_id . ' ,"' .  $id . '" ,
    				' . $s . ' ,' .  $p . ' ,
    				' . $c . ' ,' .  $g . ' , 0
    				, "")';
    		echo $sql . '<BR>' . PHP_EOL;
    		$db->sql_query($sql);
		}		
	}	
	
	echo "generate_brief_history: checking shorting stocks <BR>\n";
	
	$shorting_share 	= array();
	$shorting_cost	= array();
	$shorting_price	= array();
	$shorting_gain	= array();
	
	// get the latest user value.
	$sql2		= 'SELECT * FROM sv_user_value WHERE user_id=' . $user_id . ' ORDER BY date DESC';
	$result2 	= $db->sql_query_limit($sql2, 1);
	if (!($thisrow2 = $db->sql_fetchrow($result2)))
		return;
	
	$user_value = $thisrow2['value'];
	$threshold	= $user_value * $significant_percent;
	echo "value:$user_value, significant_percent:$significant_percent, threshold: $threshold <BR>\n";
	
	// Check the current shorting stocks.
	$sql2		= 'SELECT * FROM sv_short_stock WHERE user_id=' . $user_id;
	$result2 	= $db->sql_query($sql2);
	while ($thisrow2 = $db->sql_fetchrow($result2))
	{
		$corp_id		 	= strval($thisrow2['corp_id']);
			
		// get the latest corp price.		
		$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
		$result3 	= $db->sql_query_limit($sql3, 1); 
		if (!($thisrow3 = $db->sql_fetchrow($result3)))
			continue;			
			
		$shorting_price[$corp_id] 	= $thisrow3['price'];
		$shorting_share[$corp_id]	= $thisrow2['share'];
		$shorting_cost[$corp_id]		= $thisrow2['cost'];
		
		echo "corp_id:$corp_id share:$shorting_share[$corp_id]  cost:$shorting_cost[$corp_id]\n";		
		
		$shorting_gain[$corp_id] = ($thisrow2['cost'] - $thisrow3['price'] ) * $thisrow2['share'];
	}
	
//	print_r($holding_gain);	
//	arsort ($holding_gain);	
//	print_r($holding_gain);	
	
	foreach ($shorting_gain as $id => $g) 
	{
    	//echo "gain/loss: $id = $g<BR>";    	
    	if (abs($g) <= $threshold)
    		continue;
    	    		
    	$s	= $shorting_share[$id];
    	$c	= $shorting_cost[$id];
    	$p	= $shorting_price[$id];
    		
    	echo "significant gain/loss, $s shares, cost:$c, price:$p<BR>\n";
    		
    	// check if the record already exists. deal_time is 1 for shortding stocks.
    	$sql2		= 'SELECT * FROM sv_brief_history WHERE user_id=' . $user_id . ' AND corp_id="'. $id . '" AND deal_time=1';
		$result2 	= $db->sql_query($sql2);
		if ($thisrow2 = $db->sql_fetchrow($result2))
		{
			// update the record.
			$sql 		= 'UPDATE sv_brief_history SET share=' . $s . '
							, price=' . $p . ', cost=' . $c . '
							,gain_or_loss=' . $g . ' 
							 WHERE user_id=' . $user_id . ' AND corp_id="' . $id . '"';
							 
			echo $sql . '<BR>' . PHP_EOL;
	       	$db->sql_query($sql);			
		}
		else
		{    		
    		// add new record.
	    	$sql = 'INSERT INTO sv_brief_history (user_id, corp_id, share, price, cost, gain_or_loss, deal_time, status) VALUES (
    				' . $user_id . ' ,"' .  $id . '" ,
    				' . $s . ' ,' .  $p . ' ,
    				' . $c . ' ,' .  $g . ' , 1
    				, "")';
    		echo $sql . '<BR>' . PHP_EOL;
    		$db->sql_query($sql);
		}		
	}	
	
	echo "generate_brief_history: checking transaction history <BR>\n";
	
	// check the selling transactions for realised gain/loss.
	$sell_corp_id	= array();
	$sell_share		= array();
	$sell_price		= array();
	$sell_time		= array();
	
	$sql	= 'SELECT * FROM sv_transaction WHERE user_id=' . $user_id . ' AND action="sell" ORDER BY deal_time DESC'; 
	$result = $db->sql_query($sql);
	while ($row = $db->sql_fetchrow($result))
	{
		$corp_id	= $row['corp_id'];
		$share		= $row['share'];
		$price		= $row['price'];
		$deal_time	= $row['deal_time'];
		
		echo "checking $corp_id...<BR>\n";
		
		if (in_array($corp_id, $sell_corp_id))
		{			
			echo "array_key_exists <BR>\n";
			
			// get the indexes with the corp id.
			$index = array_keys($sell_corp_id, $corp_id);
			
			//print_r($index);
			
			$added = false;
			foreach($index as $i)
			{
				// check if the time is close enough. 
				if ($sell_time[$i] - $deal_time < $period * 24 * 60 * 60 && ($sell_share[$i] + $share) > 0)
				{
					// close enough and combine with old sales. 					
					$sell_price[$i]		= ($price * $share + $sell_share[$i] * $sell_price[$i]) / ($sell_share[$i] + $share);
					$sell_share[$i]		+= $share;
				
					$added = true;
					break;
				}
			}
			
			if ($added == false)
			{
				// add a new record for this sale. 
				$sell_corp_id[]		= $corp_id;
				$sell_share[]		= $share;
				$sell_price[]		= $price;
				$sell_time[]		= $deal_time;
			}	
		}
		else
		{
			// add a new record for this sale. 			
			$sell_corp_id[]		= $corp_id;
			$sell_share[]		= $share;
			$sell_price[]		= $price;
			$sell_time[]		= $deal_time;			
		}
	}
	
//	print_r($sell_corp_id);	echo "<BR>";
//	print_r($sell_share);echo "<BR>";
//	print_r($sell_price);echo "<BR>";
//	print_r($sell_time);echo "<BR>";
	
	$sell_gain = array();
	$sell_cost = array();
	
	// check cost.
	$i = 0;
	while ($i < sizeof($sell_corp_id))
	{
		$total_buy_share 	= 0;
		$total_buy_cost 	= 0;
			
		$sql2	= 'SELECT * FROM sv_transaction WHERE user_id=' . $user_id . ' AND corp_id="' . $sell_corp_id[$i] . '" AND action="buy" AND deal_time < ' . $sell_time[$i] . ' ORDER BY deal_time DESC';
		$result2 = $db->sql_query($sql2);
		while ($row2 = $db->sql_fetchrow($result2))
		{
			$buy_share		= $row2['share'];
			$buy_price		= $row2['price'];
			
			$total_buy_share 	+= $buy_share;
			$total_buy_cost 	+= ($buy_price * $buy_share);
		}
		
		// prevent divided-by-zero error
		if ($total_buy_cost < 0.001 || $total_buy_share < 1)
		{
			$i = $i+ 1;
			continue;
		}
			
		$avg_cost = $total_buy_cost / $total_buy_share;
		echo $sell_corp_id[$i];
		echo ", total buy share:$total_buy_share, total_buy_cost:$total_buy_cost, avg_cost:$avg_cost<BR>\n";
		
		$sell_gain[$i] = $sell_share[$i] * ($sell_price[$i] - $avg_cost);
		$sell_cost[$i] = $avg_cost;		
		
		if (abs($sell_gain[$i]) > $threshold)
		{
			// check if the record already exists.
	    	$sql2		= 'SELECT * FROM sv_brief_history WHERE user_id=' . $user_id . ' AND corp_id="'. $sell_corp_id[$i] . '" AND deal_time=' . $sell_time[$i];
	    	echo $sql2 . "-SQL2<BR>\n";
			$result2 	= $db->sql_query($sql2);
			if ($thisrow2 = $db->sql_fetchrow($result2))
			{
				// update the record.
				$sql 		= 'UPDATE sv_brief_history SET share=' . $sell_share[$i] . '
								, price=' . $sell_price[$i] . ', cost=' . $sell_cost[$i] . '
								,gain_or_loss=' . $sell_gain[$i] . ' 
								 WHERE user_id=' . $user_id . ' AND corp_id="' . $sell_corp_id[$i] . '"';
								 
				echo $sql . '<BR>' . PHP_EOL;
		       	$db->sql_query($sql);			
			}
			else
			{    		
	    		// add new record.
		    	$sql = 'INSERT INTO sv_brief_history (user_id, corp_id, share, price, cost, gain_or_loss, deal_time, status) VALUES (
	    				' . $user_id . ' ,"' .  $sell_corp_id[$i] . '" ,
	    				' . $sell_share[$i] . ' ,' .  $sell_price[$i] . ' ,
	    				' . $sell_cost[$i] . ' ,' .  $sell_gain[$i] . 
	    				' , ' . $sell_time[$i] . '
	    				, "")';
	    		echo $sql . '<BR>' . PHP_EOL;
	    		$db->sql_query($sql);
			}
		}
			
		++$i;
	}
	
	
	
}

function get_personal_setting($user_id = 0)
{
	global $db, $user;
	
	if ($user_id == 0)
		$user_id = $user->data['user_id'];

	$sql 	= 'SELECT * FROM sv_personal_setting WHERE user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		$r = array();
		
		$r['belief'] 					= utf8_normalize_nfc($row['belief']);
		$r['news_max_number'] 			= $row['news_max_number'];		
		$r['market_info'] 				= $row['market_info'];		
		$r['delegation_list_info'] 		= $row['delegation_list_info'];		
		$r['time_format'] 				= $row['time_format'];		
		$r['allow_ad'] 					= $row['allow_ad'];		
	}
	
	return $r;
}

function is_allow_ad($user_id = 0)
{
	global $db, $user;
	
	if ($user_id == ANONYMOUS)
		return 0;
		
	$allow_ad = 0;
	
	if ($user_id == 0)
		$user_id = $user->data['user_id'];

	$sql 	= 'SELECT * FROM sv_personal_setting WHERE user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		if ($row['allow_ad'] == 1)
			$allow_ad = 1;
	}
	
	$db->sql_freeresult($result);
	
	return $allow_ad;
}

function get_news_info($news_id)
{
	global $db;
	
	if ($news_id == '')
		return;
	
	$sql 	= 'SELECT * FROM sv_news WHERE news_id=' . $news_id;
	$result = $db->sql_query_limit($sql, 1);
	if ($row = $db->sql_fetchrow($result))
	{
		return $row;
	}
}

// The original version from PHPBB makes some assumptions on the url.
// If we pass FB user info in JSON format, the url may be malformed.
// This is a simple version of the redirect utility function. 
function sv_redirect($url)
{
// Redirect via an HTML form for PITA webservers
	if (@preg_match('#Microsoft|WebSTAR|Xitami#', getenv('SERVER_SOFTWARE')))
	{
		header('Refresh: 0; URL=' . $url);

		echo '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
		echo '<html xmlns="http://www.w3.org/1999/xhtml" dir="' . $user->lang['DIRECTION'] . '" lang="' . $user->lang['USER_LANG'] . '" xml:lang="' . $user->lang['USER_LANG'] . '">';
		echo '<head>';
		echo '<meta http-equiv="content-type" content="text/html; charset=utf-8" />';
		echo '<meta http-equiv="refresh" content="0; url=' . str_replace('&', '&amp;', $url) . '" />';
		echo '<title>' . $user->lang['REDIRECT'] . '</title>';
		echo '</head>';
		echo '<body>';
		echo '<div style="text-align: center;">' . sprintf($user->lang['URL_REDIRECT'], '<a href="' . str_replace('&', '&amp;', $url) . '">', '</a>') . '</div>';
		echo '</body>';
		echo '</html>';

		exit;
	}

	// Behave as per HTTP/1.1 spec for others
	header('Location: ' . $url);
	exit;
}

// Calculate maintenance rate
// 
// Parameter:
// $deposit:		deposit when shorting stocks
// $gain_or_loss:	currently unrealised gain or loss based on the latest market prices
//
// Assumption:
// Deposit is 90% of the transaction amount
// 
// maintenance rate = (deposit + transaction amount) / current market value * 100%
// 
function cal_maintenance_rate($deposit, $gain_or_loss)
{
	$transaction_amount 	= $deposit / 0.9;
	$current_market_value 	= $transaction_amount - $gain_or_loss; // for shorting, gaining means the decrasing of prices. 
	
	if ($current_market_value == 0)
		$current_market_value = 1;
		
	return ($deposit + $transaction_amount) * 100 / $current_market_value;
	
}

?>